Chandan Kumar Disclaimer

The information provided in this book is intended for educational and informational purposes only. The author and publisher have made every effort to ensure the accuracy of the content. However, the ever-evolving nature of software applications and technology means that information may become outdated or subject to change. Readers are encouraged to verify any information provided in this book and stay current with the latest updates and developments in the field. The author and publisher shall not be liable for any omissions, inaccuracies, or any actions taken based on the information contained in this book. This book is not intended as a substitute for professional advice. Always consult with the appropriate professionals if you require guidance specific to your circumstances. The author and publisher do not endorse any third-party products or services mentioned in this book, and any mention of such products or services should not be construed as an endorsement. Reading this book implies your understanding and acceptance of this disclaimer. All rights reserved. No part of this book may be reproduced in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage and retrieval system, without prior written permission from the author. The only exception is by a reviewer, who may quote short excerpts in a review. Trademarked names, logos, and images appearing in this book are the property of their respective owners. The appearance of these trademarks does not imply that the author endorses or is affiliated with these organizations.
For additional information about this book, please contact: Contact No. - +91 9667465042 Email - krmchandan@gmail.com Website – https://mylivesupport.in/
Book Name - Mastering Microsoft Excel First Edition : Dec 2023
ISBN: 9798871916599 Imprint: Independently published
Copyright © Chandan Kumar All rights reserved.

About The Author

Chandan Kumar is a Microsoft Office expert. With extensive corporate experience, he has trained over 15,000 students. Chandan also runs successful YouTube channel (My Live Support) with 400,000+ subscribers and offers video courses through his website, www.mylivesupport.in. Chandan's mission is to simplify complex software and empower learners to excel in various professional domains, including Microsoft Excel, Word, PowerPoint, Tally, Photoshop and more.

Important Information About the Excel Version

This book is written primarily for Microsoft Excel 365, which is the latest and most up-to-date version at the time of publication. Microsoft Excel 365 comes with numerous features, formulas, and functions that provide powerful capabilities for spreadsheet users.

However, we understand that not everyone may be using Excel 365. If you are using Excel 2007, 2010, 2013, 2016, 2019, 2021, or any other version, we want to assure you that this book is still valuable for you.

Here's why: While the book showcases Excel 365's latest features, many of the fundamental concepts, functions, and techniques are consistent across various Excel versions. You will find a wealth of knowledge and practical guidance on spreadsheet creation, data analysis, and automation, all of which can be applied to your specific Excel version.

Furthermore, for those using Excel versions older than 365, this book serves as an opportunity. By gaining insights into the capabilities of Excel 365, you will discover the exciting world of new features, formulas, and functions available. It's a win-win situation – you learn the essentials and get a glimpse of what's possible in more recent Excel versions.

Excel is a dynamic tool that keeps evolving, and this book helps you keep up with the latest trends while mastering the core skills. Whatever version you are using, we are confident that the knowledge and techniques shared here will enhance your Excel experience.

Start your journey toward becoming an Excel pro, and enjoy the benefits of using this versatile software for work, home, or studies.
Happy learning and excelling!

Why You Should Learn Microsoft Excel

Learning Microsoft Excel offers a wide range of benefits that can make your work and personal life easier. Here are some reasons why you should learn Microsoft Excel :

1. Boost Your Productivity: Excel allows you to automate repetitive tasks, perform complex calculations, and manage data efficiently. This can save you a lot of time and effort.

2. Enhance Your Career: Excel proficiency is a valuable skill in the job market. Many employers look for candidates who can use Excel for data analysis, reporting, and decision-making.

3. Organize Your Finances: Excel is excellent for creating budgets, tracking expenses, and managing personal finances. It can help you make better financial decisions.

4. Improve Data Analysis: Excel's features, like PivotTables and charts, make it easier to analyze data and identify trends. This is handy for businesses and individuals.

5. Create Stunning Charts and Graphs: Excel lets you turn raw data into visual representations like charts and graphs, making it easier to understand and present your information.

6. Simplify Complex Tasks: Excel can handle complex calculations, making it useful for scientific, engineering, and financial applications.

7. Lifetime Skill: Once you learn Excel, it's a skill that stays with you and can be applied in various aspects of your life.

Whether you're a student, professional, or someone managing their daily life, Excel can make tasks simpler and more efficient, making it a valuable skill to learn.

Table of Content :

Part I: Introduction to Microsoft Excel Page No.

1.1 What Is Excel, Key Feature And Uses Of Excel 1
1.2 Excel Interface and Navigation 3
1.3 Basic Terms and Concepts 7
1.4 Create And Edit Spreadsheets 12

Part II: Data Entry and Management

2.1 Operators in Excel 17
2.2 Entering, Editing, and Deleting Data 20
2.3 Generating Serial Numbers and Dates 23
2.4 Insert, Delete Row, Column, Sheets 26
2.5 Adjusting Row Height And Column Width 33
2.6 Hiding and Unhiding Rows and Columns 37
2.7 File Menu or Tab and Its Functions 41
2.8 Undo and Redo Operations 44
2.9 Saving Excel Data 47

Part III: Data Formatting and Styling

3.1 Cut, Copy, Paste, Format Painter and Clipboard 50
3.2 Formatting Text with Font Group 54
3.3 Aligning Data with Alignment Group 58
3.4 Manage Numbers with Number Group 65 3.5 Applying Conditional Formatting 76 3.6 Formatting Data as Tables 90 3.7 Utilizing Cell Styles 92 3.8 Dealing with Large Numbers 94 3.9 Paste Special Options 96 3.10 Creating Headers and Footers 100 3.11 Inserting and Managing Comments 103 3.12 Go To Special Options 105 3.13 Using the View Tab 112 3.14 Freezing Panes for Data Analysis 116

Part IV: Working with Data

4.1 Sorting Data 119
4.2 Filtering Data 126
4.3 Working with Random Numbers 134
4.4 Adding Illustrations 137
4.5 Creating Charts 141
4.6 Incorporating Hyperlinks 146
4.7 Text to Columns Function 153
4.8 Removing Duplicates 157
4.9 Using Flash Fill 160
4.10 Transposing Data 166
4.11 Copy and Paste Visible Data 169 4.12 Running Total with Quick Analysis 170 4.13 Restore Sorted Data 172 4.14 Filter Data with Blank Rows 174 4.15 Calculate Age 176 4.16 Basic Dropdown List 178 4.17 Dynamic & Unique Drop Down List 183 4.18 Save Excel Sheet In PDF 187 4.19 Save Excel Selected Area In PDF 189 4.20 Link Any Excel Data With Ms Word 192 4.21 Page Setup and Print Data 194

Part V: Formulas and Functions

5.1 Understanding Formulas And Function 204
5.2 Creating Formulas And Functions 205
5.3 Sum Function 209
5.4 Subtracting Data 214
5.5 Multiplying Data 216
5.6 Division Data 219
5.7 Average Function 221
5.8 Percentage Calculation 224
5.9 Count Counta And Countblank Function 227
5.10 Lower Proper And Upper Function 230
5.11 Contactenate Function 233 5.12 Value Function 236 5.13 Text Function 240 5.14 Copying and Pasting Formula 242 5.15 Subtotal Function 248 5.16 Subtotal Function Number Difference 252 5.17 Goal Seek Function 254 5.18 Aggregate Function 257 5.19 Trim Function 261 5.20 Len Function 263 5.21 Abs Function 267 5.22 Rank Function 269 5.23 Pmt Function 272 5.24 Date And Time Function 275

Part VI: Advanced Formulas and Functions

6.1 Vlookup Function 278
6.2 Hlookup Function 284
6.3 Xlookup Function 287
6.4 Unique Function 293
6.5 Filter Function 296
6.6 Minifs And Maxifs Functions 301
6.7 Sumif Function 305
6.8 Sumifs Function 307 6.9 Absolute Reference 310 6.10 AverageIf Function 318 6.11 Averageifs Function 323 6.12 Countif Function 327 6.13 Countifs Function 332 6.14 If Function 336 6.15 Ifs Function 341 6.16 If Function With And, Or, Not Functions 344 6.17 Index Function 353 6.18 Match Function 356 6.19 Using Index And Match Together 358 6.20 TextJoin Function 363

Part VII: Advanced Data Analysis

7.1 Pivot Table for Summary & Reports 366
7.2 Slicer Function 388
7.3 Pivot Chart 393
7.4 Timeline Feature 397

Part VIII: Workbook Security and Sheet Management

8.1 Protect And Unprotect Excel Workbook 401
8.2 Rename, Delete, Hide, Protect A Sheet 404
8.3 Add And Remove Password From A Excel File 408

Part IX: Useful Excel Shortcuts 412

Accessing Practice Files

Download Practice File and images from here : http://bit.ly/3uXPPUS

Part I: Introduction to Microsoft Excel

Chapter 1.1 - What is Excel, Keyfeature & Uses ?

Microsoft Excel is a popular spreadsheet software application developed by Microsoft. It's part of the Microsoft Office suite, which includes other productivity tools like Microsoft Word, PowerPoint, and Outlook. Excel is widely used for various tasks involving data organization, analysis, and visualization.

Here are some key features and uses of Microsoft Excel:

1. Data Entry and Organization: Excel provides a grid-like interface where you can input and organize data into rows and columns. This makes it easy to create tables, lists, and databases.

2. Calculations and Formulas: Excel is equipped with a wide range of mathematical and logical functions. Users can perform calculations, apply formulas, and automate data analysis tasks.

3. Data Analysis: It offers powerful tools for data analysis, including sorting, filtering, and creating PivotTables and PivotCharts for summarizing and visualizing data.

4. Charts and Graphs: Excel allows users to create various types of charts and graphs to visually represent data trends and patterns. Common chart types include bar charts, pie charts, and line charts.

5. Conditional Formatting: Users can apply formatting rules to cells based on specified conditions. This helps highlight important data points and trends within a dataset.

6. Data Validation: Excel allows you to set rules and restrictions for data input to ensure accuracy and consistency.
7. Automation and Macros: Advanced users can create macros using Visual Basic for Applications (VBA) to automate repetitive tasks and enhance functionality.

8. Data Integration: Excel can connect to external data sources like databases and web services, allowing users to import and refresh data from various locations.

9. Collaboration: Excel offers features for collaborating on spreadsheets in real-time, including co-authoring and sharing through cloud services like Microsoft OneDrive and SharePoint.

10. Templates and Add-Ins: It provides a library of templates for common tasks and offers the ability to extend functionality with add-ins and custom solutions.

11. What-If Analysis: Excel supports scenario analysis and goal seeking to help users make informed decisions by exploring different variables and outcomes.

12. Financial and Statistical Analysis: Excel is widely used in finance for tasks such as budgeting, financial modeling, and investment analysis. It also includes statistical functions for data analysis.

Excel is employed across a wide range of industries and professions, including finance, accounting, marketing, research, education, and more. It's a versatile tool for managing and analyzing data, making it a valuable asset for both individuals and organizations.

Chapter 1.2 Excel Interface and Navigation

Let's explore the Excel interface along with their uses and benefits: Imagine Excel as your virtual office for working with data. Here's a tour of the main parts:
Workbook NameWindow Control Quick Access Toolbar
Show Hide Ribbon
Formula Bar Collapse/Expand Ribbon
Name Box Ribbon
Active Cell
Active Row NumberSheet
Worksheet Tabs Columns Header
Scroll Bar
Cell
View Option
StatusBar Zoom Slider
1. Quick Access Toolbar:
• Use: Offers quick access to frequently used commands like Save Undo and Redo, saving time.
• Benefit: Streamlines your workflow by allowing you to perform common tasks with a single click.

2. Workbook Name:
• Use: Displays the name of your current Excel workbook.

• Benefit: Helps you identify and switch between different Excel files.
3. Show/Hide Ribbon:
• Use: Toggles the visibility of the Ribbon to maximize workspace.
• Benefit: Maximizes your workspace and minimizes distractions when working with your data.

4. Window Control:
• Use: Minimize, maximize, or close the Excel window.
5. Name Box:
• Use: Displays the cell reference or name of the selected cell.
6. Formula Bar:

• Use: Displays and allows editing of cell contents, including formulas.

• Benefit: Enables precise data entry and editing of formulas. 7. Ribbon:

• Use: Contains Excel's commands organized into tabs and groups.
• Benefit: Provides easy access to a wide range of functions, making your tasks more efficient.

8. Collapse/Expand Ribbon:
• Use: Temporarily hides or reveals the Ribbon for more screen space.
• Benefit: Tailors your workspace to your needs, especially on smaller screens.

9. Scroll Bar (Horizontal and Vertical):
• Use: Allows scrolling through large spreadsheets.

• Benefit: Navigates your data, ensuring you can access all parts of your worksheet.

10. Column:
• Use: Vertical sections in a worksheet, identified by letters.
11. Active Cell:
• Use: Indicates the currently selected cell.
12. Cell:
• Use: Individual unit where data is entered or displayed.

• Benefit: Stores information and allows for calculations and data organization.
13. Row:
• Use: Horizontal sections in a worksheet, labeled with numbers.
14. Worksheet Tabs:

• Use: Tabs that represent different sheets in the same workbook.
• Benefit: Facilitates organization by allowing you to work with multiple sheets in one file.

15. Active Sheet:
• Use: The currently selected worksheet in your workbook.

• Benefit: Keeps your focus on the sheet you're working on, preventing errors.
16. Status Bar:
• Use: Displays information about the current worksheet, like sum or average calculations.
• Benefit: Provides quick insights into your data without complex formula entry.
17. View Option:
• Use: Changes the way your worksheet is displayed (Normal, Page Layout, Page Break Preview).
• Benefit: Allows you to tailor your view to suit your editing and printing needs.
18. Zoom Slider:
• Use: Adjusts the zoom level to make your data larger or smaller on the screen.
• Benefit: Enhances visibility and readability, accommodating different preferences and screen sizes.

So, the Excel interface is your digital playground where you create, analyze, and organize data. Each part has its role, making your work easier and more efficient, just like having all the tools you need in your workspace.

Chapter 1.3 Basic Terms And Concepts

Here are some basic terms and concepts in Microsoft Excel explained: 1. Workbook:

- Definition: A workbook is the main Excel file that contains your data, worksheets, and charts. It's like a digital notebook where you organize your information.

2. Worksheet:

- Definition: A worksheet, also known as a spreadsheet or sheet, is a single page within an Excel workbook. It consists of rows and columns where you can enter and manipulate data.

3. Cell Formatting:

- Definition: Cell formatting involves changing the appearance of a cell, including its font, color, number format, and borders. It helps improve the visual presentation of data.

- Example: You can format a cell to display dates in a specific format, such as "MM/DD/YYYY."
4. Cell:
- Definition: A cell is a single box within a worksheet where you can enter and display data. Cells are identified by their unique column and row labels.
5. Column:

- Definition: A column is a vertical arrangement of cells labeled with letters (A, B, C, etc.). Columns are used to organize and categorize data vertically.

Rows 6. Row:

- Definition: A row is a horizontal arrangement of cells labeled with numbers (1, 2, 3, etc.). Rows are used to organize and categorize data horizontally.

7. Cell Address:
Columns
Active Cell C8 : Refers to Column C and Row No. 8

- Definition: A cell address is a combination of its column letter and row number (e.g., A1, B2). It uniquely identifies a cell within a worksheet.
- Example: Cell C8 (Screen Shot) refers to the cell in the third column (Column C) and fourth row (Row Number 2) of a worksheet.
- Example: Cell A5 refers to the column A and Row Number 5 of a worksheet
8. Formula:

- Definition: A formula is an equation used to perform calculations in Excel. It usually begins with an equals sign (=) and can include cell references, functions, and operators.
- Example: The formula "=A1 + B1" adds the values in cells A1 and B1.

Suppose you have numbers in cells A1 (50) and B1 (60). To add them together, you use a formula in cell C1:

=A1 + B1 (Table 1)
(Type formula in cell C1)
After pressing Enter, Excel calculates and displays the result:

• C1 = 110(Table 2) Table 1

9. Chart:

Table 2
Definition: A chart is a visual representation of data, often used to illustrate trends, comparisons, or relationships. Excel offers various chart types.

10. Function:

- Definition: A function is a predefined formula in Excel that performs specific tasks or calculations. Functions are used to simplify complex calculations.

- Example: Let's say you have a list of numbers in cells A1 through A5 like this (see table 1):

To find the total, you can use the SUM function in cell A6. Simply write =SUM(A1:A5)(See Table 2), and Excel will add the numbers in that range for you.

In this case, the SUM function calculates and displays the result, which is 130 in cell A6(See Table 3). Table 1
Table 2
Table 3

11. Filter:

- Definition: Filtering allows you to display only specific data based on criteria. It helps you analyze and focus on relevant information.
- Example: You can filter a list of products to show only those with prices above $50.

12. Sort:

- Definition: Sorting rearranges data in ascending or descending order based on a chosen column. It's useful for organizing data alphabetically or numerically.
- Example: Sorting a list of names in ascending order would arrange them from A to Z.

These basic terms and concepts are the building blocks of Excel, and understanding them is essential for effectively working with spreadsheets and data.

Chapter 1.4 - Create and Edit Spreadsheets

Let's understand how to use Excel to create and edit spreadsheets:

Step 1: Opening Excel:

- First, find the Excel icon (it's a green "X") on your
computer or in your Office apps.

- Double-click it to open Excel. Imagine this as opening a blank canvas for your data.

Step 2: Creating a New Spreadsheet:

- When Excel opens, you'll see a blank
spreadsheet called a " Blank workbook."
It's like a big digital notepad. Click on it.

- In the workbook, you have different
sheets (tabs at the bottom) to work on
different parts of your project. You usually

start with "Sheet1."

Step 3: Entering Data:

- Click on a cell (a little square in the grid) and
start typing. You can enter numbers, text, or
anything you want. It's like typing on a digital

typewriter.

Once you've entered your data, such as numbers

or text, simply press 'Enter' to move to the cell
below or 'Tab' to move to the cell on the right. Of course, you can also use the arrow keys for navigation.

Step 4: Formatting Your Data:

- To make your data look nice, you can format it. Imagine this as choosing a fancy font, bolding important words, or highlighting cells.
- Select the cell or cells you want to format, then use the formatting tools at the top. It's like using crayons to make your work colorful.

Step 5: Using Formulas:

- Excel is really smart with math. You can use it to do calculations. For example, you can add numbers or find an average.
- To do this, type an equal sign (=) in a cell, then type your formula. For instance, "=A1+B1" adds the numbers in cells A1 and B1.
(See below table 1 for formula and table 2 for result)
Table 1
Table 2
Step 6: Creating Charts (Optional):
- Sometimes, numbers are easier to understand when you see them as charts or graphs. Excel can do that too!

1. Select your data
2. Go to the "Insert" tab
3. Pick your chart, and you're done! It's like turning numbers into pictures.

Figure 1

Note – Do Not Select Total Amount.
Step 7: Editing and Updating:

- If you make mistakes or want to change something, just click on the cell and start typing. It's like editing a text document.
- Hit the "Enter" key to confirm your entry. Your data will be stored in the cell you selected.
Step 8: Saving Your Work:

- Don't forget to save your spreadsheet so you don't lose your work. Click the little floppy disk icon(see figure 2)or use " Ctrl+S" (or "Cmd+S" on Mac).

Figure 2
Figure 3
- Give your file a name and choose where to save it, like you would with any other document(see figure 3).

Step 9: Closing Excel:

- When you're done, click the "X" in the top right corner, just like closing a book.

Step 10: Reopening Your Work:

- Next time you want to edit your spreadsheet, open Excel, then open your saved file (like opening a book you left on the shelf).

- To open an Excel file, you can either click on 'Open' or use the keyboard shortcut Ctrl + O. You'll see a list of your recently accessed Excel files.

Choose the one you want to work on. If you can't find your file, click on

'Browse' to locate and open it in Excel.

And that's it! You've got the basics of creating and editing spreadsheets with Excel. It's like having a digital notebook that's also a math whiz, an artist, and a data organizer—all in one!

Part II: Data Entry and Management

Chapter 2.1 Operators in Excel

In Excel, operators are like the tools you use to perform various operations on your data. They are symbols or characters that tell Excel what kind of calculation or comparison you want to make. Think of them as the "action words" in your Excel formulas and functions. Here's a explanation of operators in Excel:

1. Arithmetic Operators:

• Addition (+): This operator is like a plus sign in math. You use it to add numbers together. For example, "A1 + B1" adds the contents of cells A1 and B1.

• Subtraction (-): Just like in math, the minus sign helps you subtract one number from another. "A1 - B1" subtracts the value in B1 from A1.

• Multiplication (*): An asterisk (*) is your multiplication buddy. Use it to multiply numbers. "A1 * B1" multiplies the contents of A1 and B1.

• Division (/): The forward slash (/) helps you divide numbers. "A1 / B1" divides the value in A1 by B1.

• Exponentiation (^): The caret (^) is like a superpower symbol. It raises one number to the power of another. "A1 ^ B1" raises A1 to the power of B1.

2. Comparison Operators:

• Equal To (=): This operator checks if two values are equal. "A1 = B1" returns TRUE if A1 is equal to B1; otherwise, it's FALSE.

• Not Equal To (<>): The less than and greater than signs combined with an equal sign check if two values are not equal. "A1 <> B1" returns TRUE if A1 is not equal to B1.

• Greater Than (>): The greater than sign checks if one value is greater than another. "A1 > B1" returns TRUE if A1 is greater than B1.

• Less Than (<): The less than sign checks if one value is less than another. "A1 < B1" returns TRUE if A1 is less than B1.

• Greater Than or Equal To (>=): This operator checks if one value is greater than or equal to another. "A1 >= B1" returns TRUE if A1 is greater than or equal to B1.

• Less Than or Equal To (<=): It checks if one value is less than or equal to another. "A1 <= B1" returns TRUE if A1 is less than or equal to B1.

3. Concatenation Operator:

• Ampersand (&): This operator is like a digital glue. It combines text or values from different cells. "A1 & B1" combines the contents of A1 and B1 into a single text string.

Operators are your friends in Excel, helping you perform calculations and comparisons effortlessly. They play a crucial role in building formulas and functions that make your spreadsheet magic happen.
Here's a table summarizing the operators in Excel:

Arithmetic Operators:

Operator Description + Addition
- Subtraction

Example Result
A1 + B1 Sum of A1 and B1 A1 - B1 A1 minus B1

* Multiplication A1 * B1 Product of A1 and B1 / Division A1 / B1 A1 divided by B1 ^ Exponentiation A1 ^ B1 A1 raised to the power of

B1

Comparison Operators:

Operator Description = Equal To <> Not Equal To

> Greater Than
< Less Than
>= Greater Than or

Equal To
<= Less Than or
Equal To

Example Result
A1 = B1 TRUE if A1 equals B1 A1 <> B1 TRUE if A1 doesn't equal

B1
A1 > B1 TRUE if A1 is greater
than B1
A1 < B1 TRUE if A1 is less than
B1
A1 >= B1 TRUE if A1 is greater
than or equal to B1 A1 <= B1 TRUE if A1 is less than or
equal to B1

Concatenation Operator:

Operator Description Example Result
& Concatenation A1 & B1 Contents of A1 and B1 combined
These operators are essential tools for creating formulas and performing comparisons in Excel.

Chapter 2.2 Entering, Editing, and Deleting Data

Let's explore entering, editing, and deleting data in Excel.

Entering Data into Excel:

Entering data into Excel is like filling in a digital table. Here's how to do it: 1. Select a Cell: Click on the cell where you want to enter data. Think of a cell as a box in your digital spreadsheet.
2. Type Your Data: Start typing your data. You can enter text, numbers, dates, or even formulas. For instance:

- Date Data: In cell A1, type "Date"
- Text Data: In cell B1, type "Product Name"
- Numeric Data: In cell C1, type "Quantity"
- Numeric Data: In cell D1, type "Rate"
- Amount Calculation : In cell E1, type "Amount"

Formulas: In cell E2, type =C2*D2 to calculate the Amount. To calculate amounts for the entire table, you can copy the formula from E2 and paste it into cells E3 to E6.

3. Press Enter: Hit the "Enter" key to confirm your entry. Your data will be stored in the cell you selected.

Editing Data in Excel:

Editing data in Excel is like making corrections on a digital page. Here's how you can do it:
1. Select the Cell: Click on the cell you want to edit. The cell's content will be highlighted.

2. Edit the Data: Modify the data as needed. For example:
- Text Data Edit: Change " Product" to "Product Description."
- Numeric Data Edit: Update the price of Apple from 150 to 125.

3. Press Enter: After making changes, press "Enter" to confirm your edits. The cell will now display the updated data.

Deleting Data in Excel:

Deleting data in Excel is like erasing information on a digital whiteboard. Here's how to do it:
1. Select the Cell(s): Click on the cell or cells you want to delete. They will be highlighted.
2. Delete: Press the "Delete" key on your keyboard. The selected cell contents will be removed.

3. Clear All: If you want to delete all content from a cell but keep the cell itself and it’s formatting, you can right-click the cell, choose "Clear Contents," and it will become empty.

Examples:

- Entering Data: Imagine you're creating a sales list. You enter "Product Name" in cell A1, "Price" in cell B1, and "Date Added" in cell C1. In cell A2, you type "Laptop," in B2, you enter "$800," and in C2, you put "07/15/2023."

- Editing Data: Later, you realize the price is incorrect. You click on cell B2, change it to "$900," and press "Enter" to update it.
- Deleting Data: You decide to remove the date information. You select cell C2 and press "Delete" to clear the date. The cell is now empty.

In Excel, entering, editing, and deleting data are essential skills for managing your spreadsheet effectively. Think of it as writing, correcting, and erasing information on a digital notepad.

Chapter 2.3 Generating Serial Numbers and Dates

You can generate serial numbers, day names, month names, years, and quarters in Excel without using complex formulas by simply dragging the autofill handle. Let’s learn how to generate Serial Numbers and Dates.

Generating Serial Numbers:

To generate serial numbers in Excel, follow these simple steps:
1. Start with the first cell where you want your serial numbers to begin. In that cell, enter "1".
2. In the next cell (where you want your serial numbers to start counting), enter "2".
3. Now, select both of these cells.

4. Move your cursor to the lower-right corner of the selected cell (you'll see a small square, which is called the autofill handle).(see image 1)

5. As you hover over this handle, your cursor will change into a plus icon.(see image 2)
Image 1
Image 2
6. Once you see the plus icon, left-click and hold your mouse button, then drag it downwards.

7. The further down you drag, the more serial numbers will be automatically generated. They'll fill the number of cells

you've dragged through.(see image 3)
Image 3

Generating Day Names:

1. Enter the full name of the day (e.g., "Sunday")
in the first cell where you want to start.

2. Click and drag the autofill handle down to fill
the desired number of cells. Excel will
automatically generate day names.

Generating Short Day Names:

1. Enter the short name of the day (e.g., "Sun") in the first cell where you want to start.

2. Click and drag the autofill handle down to fill the desired number of cells. Excel will generate short day names.

Generating Month Names:

1. Enter the full name of the month (e.g., "January") in the first cell.

2. Click and drag the autofill handle down to fill the desired number of cells. Excel will automatically generate month names.

Generating Short Month Names:

1. Enter the short name of the month (e.g., "Jan") in the first cell.

2. Click and drag the autofill handle down to fill the desired number of cells. Excel will generate short month names.

Generating Years:
1. Enter the starting year in the first cell e.g. "2021" and second cell 2022).
2. Select Both Cell e.g. 2021 and 2022

3. Click and drag the autofill handle down to fill the desired number of cells with consecutive years.

Generating Quarters:
1. Enter the first quarter (e.g., "Q1" or "Quarter 1") in the first cell.

2. Click and drag the autofill handle down to fill the desired number of cells. Excel will automatically generate consecutive quarters.

By using this method, Excel will recognize the pattern and continue to generate the series accordingly. It's a quick and easy way to populate your spreadsheet with sequential data without the need for complex formulas or functions.

Chapter 2.4 Insert, Delete Row, Column, Sheets

Let’s learn how to insert and delete cells, rows, columns, and sheets in Microsoft Excel :

Inserting Cells:

- Select a Cell: Click on the cell where you want to insert new cells. Excel will insert cells below the selected cell for rows or to the right for columns. Based on the images below, we've selected the cell named "Ramesh," which refers to cell B2.

- Right-click and Choose "Insert" : Right-click on the selected cell, and from the context menu, choose "Insert." You can then specify whether you want to insert entire rows or columns or cell(see image 1 and 2).

Image 1 Image 2

Example : Suppose you've selected a cell and decided to shift it to the right by choosing the "Shift cells right" option. After confirming by clicking "OK," you'll notice that the original cell's content has moved to the right, and a

blank cell has taken its place.(see image 3) Image 3

- Home Tab: You can also go to the Home tab in the ribbon and click the "Insert" dropdown. Here, you can choose to insert cells, rows, or columns.

Inserting Rows and Columns:
Inserting Rows

To insert a blank row in Excel, you can follow these step-by-step instructions:

1. Select the Row: Click on the row number of the row below where you want to insert a blank row. The row number are labeled with number (1, 2, 3, etc.) at the left of your sheet.This will select the entire row.

2. Right-Click: After selecting the row, right-click on it. A context menu will appear.
3. Insert: From the context menu, choose " Insert." Excel will add a new, blank row above the row you initially selected.
4. Complete Your Edit: You can now enter data into the newly inserted row or make any adjustments you need.
Figure 4

Insert Columns

To insert a blank column in Excel, you can use these step-by-step instructions:

1. Select the Column Header: Click on the column header letter at the right of where you want to insert a new blank column. The column headers are labeled with letters (A, B, C, etc.) at the top of your sheet. Click on the column header that's left of where you want the new blank column to appear. This action selects the entire column. (see image 1)

2. Right-Click: After selecting the column, right-click on it. A context menu will appear.

3. Insert: From the context menu, choose "Insert." Excel will add a new, blank column to the left of the column you initially selected. (see image 2)

4. Complete Your Edit: You can now enter data into the newly inserted column or make any adjustments you need.(see image 3)
Image 1
Image 2
Image 3

Inserting Sheets:

To insert a new sheet in Excel, simply go to the bottom of your sheet tabs, where you'll find a small plus icon (+). Click on this plus icon, and a new sheet will be added to your workbook.

Home Tab for Insert

To insert cells, rows, columns, or sheets, you can
also go to the 'Home' tab and find the 'Insert'
dropdown. From there, you can choose to insert
cells, rows, columns, or sheets as needed.

Note: To insert multiple columns or rows in Excel, follow these steps:

1. Select Multiple Rows or Columns: Click and drag your cursor to select the number of rows or columns you want to insert. They will be highlighted.

2. Right-Click and Choose Insert: After making your selection, rightclick on the highlighted rows or columns. From the context menu that appears, select the 'Insert' option. You will see Multiple rows or columns will be inserted based on your selection.

This allows you to efficiently insert several rows or columns at once to accommodate your data."

Deleting Cells, Rows, Columns, and Sheets
Deleting Cells:

- Select Cells: Click on the cell or cells you want to delete. In the example images below, we've clicked on the cell named 'Suresh,' which corresponds to cell B3(see image 1).

- Right-click and Choose "Delete": Right-click on the selected cell(s), and from the menu that appears, select 'Delete'(see image 1). You can choose to shift the surrounding cells either upward or to the left to fill the gap(see image 2).

Once you've done this, you'll notice that the selected cell 'Suresh' has been deleted, and the cells below it have moved up, as we selected the second option to shift cells up from the delete window.(see image 3)

Image 2
Image 1
Image 3

Deleting Rows

1. To delete rows, first select the row(s) you want to remove.
2. Right-click your selection, and then choose 'Delete.' The selected rows will be deleted, and the rest of the rows will adjust accordingly.

In the example image, we've chosen to delete the fourth row, which contains data for a person named 'Manoj(see image 1).'After deleting this row, all of the data in the 'Manoj' row will be erased, and the rows below it will move up to fill the space(see image 2).

Image 1

Delete Columns

- Select the column(s) you want to delete, right-click, and choose Delete. The columns will be removed, and the remaining columns will adjust

accordingly.
Image 2

Deleting Sheets

- Delete Sheets: To delete a sheet, right-click on its tab at the bottom of the Excel window and choose " Delete." You can confirm the deletion in the prompt that appears.

Benefits:
Sheet 2 Sheet Deleted Now
- Data Adjustment: Inserting and deleting cells, rows, and columns allows you to adjust the structure of your data as needed.
- Sheet Management: Adding and removing sheets helps you organize your workbook and keep it clutter-free.

In summary, the ability to insert and delete cells, rows, columns, and sheets in Excel gives you flexibility in managing your data and workbook. It's like adjusting the pieces of a jigsaw puzzle to create the perfect picture of your information.

Chapter 2.5 Adjusting Row Height, Column Width

Adjusting row height and column width in Excel is like resizing a shelf to fit different-sized books. It helps you control how much content is visible within each cell. Here's how to do it:

Increasing/Decreasing Row Height:

- Increase Row Height: To make a row taller, follow these steps: 1. Hover your mouse pointer over the line between the row numbers (for example, between row 1 and row 2).
2. The pointer will change to a double-headed arrow(see image 1). Click and drag it downward to increase the row height.
Image 1
Image 2
3. Release the mouse button when the row is the desired height(see image 2).
- Decrease Row Height: To make a row shorter:
1. Hover your mouse pointer over the line between the row numbers(see above image 1).
2. Click and drag it upward to decrease the row height.
3. Release the mouse button when the row is the desired height.

Increasing/Decreasing Column Width:

- Increase Column Width: To make a column wider:
1. Hover your mouse pointer over the line between the column letters (for example, between column A and column B)(see image 1).
2. The pointer will change to a double-headed arrow. Click and drag it to the right to increase the column width.(see image 2)
3. Release the mouse button when the column is the desired width.(see image 3)
Image 1
Image 2 Image 3

- Decrease Column Width: To make a column narrower: 1. Hover your mouse pointer over the line between the column letters. 2. Click and drag it to the left to decrease the column width. 3. Release the mouse button when the column is the desired width.

Autofit Rows and Columns:

- Autofit Row Height: To automatically adjust the row height to fit the content in the tallest cell in the row:

1. Double-Click the Line: Locate the row number that you want to autofit. Then, move your cursor to the line between that row number and the next one. When you see a double-headed arrow, double-click it. Excel will automatically adjust the row's height to fit the content in the tallest cell in that row.

2. All rows with more content than the auto-fitted row will expand to show their content.
Before
After
- Autofit Column Width: To automatically adjust the column width to fit the widest cell in the column:
1. Double-click the line between the column letters. Excel will adjust the column width based on the content in that column.
2. All columns with wider content than the auto-fitted column will expand to display their content. Before Autofit After Autofit

Important- To autofit all rows and columns in your Excel sheet

simultaneously, follow these steps:

1. Select the Entire Sheet: Click on the 'Select Entire Sheet' icon located at the intersection of the top-left cell (usually A1) where rows and columns meet.

2. Double-Click: Now, with the entire sheet selected, move your cursor to the line between any two row numbers or any two column letters (for example, between rows 1 and 2 or columns A and B). When you see a double-headed arrow, double-click. This action will autofit all rows and columns in your sheet at once, ensuring that all content is clearly visible."

Chapter 2.6– Hiding, Unhiding Rows and Columns

Hiding and unhiding rows and columns in Excel is a useful feature when you want to focus on specific parts of your data or make your worksheet more organized. Here's a step-by-step explanation with examples:

Hiding Rows or Columns:
Step 1: Select the Rows or Columns

1. To hide rows, click on the row number on the left-hand side of the worksheet to select the entire row. For columns, click on the column letter at the top of the worksheet to select the entire column. You can select multiple rows or columns by holding down the Ctrl key while clicking.

Step 2: Right-click and Choose "Hide"
2. Once you've selected the rows or columns, right-click on any of the selected rows or columns. A context menu will appear.
3. From the context menu, select "Hide." Excel will hide the selected rows or columns.
Example - Hiding Rows:
Suppose you have a table like this:
Now, let's say you want to hide the second and fourth rows (John and Michael). Here's how you do it:

1. Click on row 2 to select it.
2. Hold down the Ctrl key and click on row 4 to select it as well.
3. Right-click on any of the selected rows (either row 2 or 4).
4. Choose "Hide" from the context menu.

Figure 5
Now, rows 2 and 4 are hidden, and your table will look like this:

Unhiding Rows or Columns:

Step 1: Select the Rows or Columns Surrounding the Hidden Ones

1. To unhide rows, click on the row number above and below the hidden rows. To unhide columns, click on the column letters to the left and right of the hidden columns.

Step 2: Right-click and Choose "Unhide"
2. Right-click on any of the selected rows or columns, and from the context menu, select "Unhide."

Important : For unhiding all rows or columns in your sheet at once,

follow these steps:

1. Select your entire sheet by clicking on the 'Select Entire Sheet' icon, which is located at the top left corner where the first row and column A meet.

2. Right-click on any row number or column header.
3. From the menu that appears, select the 'Unhide' option.

This will unhide all the rows and columns in your sheet, making everything visible again.
Example - Unhiding Rows:
Let's continue from the previous example where we hid rows 2 and 4.

1. Click on row 1 and hold the left mouse button, then drag it to select rows from 1 to 5 (we need to select the hidden rows). Rows 2 and 4 are hidden between them.

2. Right-click on any of the selected rows.
3. From the context menu, choose "Unhide."

This action will make rows 2 and 4 visible again, ensuring your entire table is fully displayed.
Figure 6
Figure 8
Figure 7

Hiding and unhiding rows and columns in Excel can help you manage and present your data more effectively, especially when dealing with large datasets or when you want to focus on specific parts of your worksheet.

Chapter 2.7 File Menu or Tab and It’s Function

The "File" menu or Tab in Excel contains essential options for managing your workbooks, including creating, opening, saving, and printing files. Let's explore each option.

1. New:

- What It Does: Allows you to create a new Excel workbook from scratch or choose from various templates.

- Example: You can use "New" to start a blank workbook for a new project, or you can select a template like "Blank workbook" or "Budget" to kickstart your work.

2. Open:

- What It Does: Lets you open an existing Excel file from your computer or cloud storage.

- Example: You can use "Open" to access a spreadsheet you've saved before, such as a monthly sales report, by browsing your files and selecting the one you want to open.

3. Save:

- What It Does: Saves your current workbook with its current name and location.

- Example: After making changes to your spreadsheet, you can click "Save" to overwrite the existing file with the updated content.

4. Save As:

- What It Does: Allows you to save your workbook with a different name, location, or file format.

- Example: You can use "Save As" to create a copy of your workbook with a new name (e.g., "Budget_Q2" instead of "Budget_Q1") or save it in a different folder or format like PDF.

5. Print:

- What It Does: Opens the print dialog, allowing you to print your workbook or configure print settings.

- Example: When you want to generate a hard copy of your Excel data, you can click "Print" to access options like choosing a printer, setting page orientation, and selecting the number of copies.

6. Share:

- What It Does: Provides options for sharing your workbook with others, such as sending it as an email attachment or saving it to OneDrive for collaborative editing.

- Example: If you're working on a team project, you can use "Share" to invite colleagues to view or edit the same workbook online.

7. Export:

- What It Does: Allows you to export your workbook data to other file formats like PDF, CSV, or HTML.
- Example: You can export a financial report as a PDF to share with others who may not have Excel, ensuring they can view it without any issues.

8. Close:

- What It Does: Closes the current workbook without exiting Excel itself.
- Example: After finishing work on one spreadsheet, you can click "Close" to return to the Excel start screen or open another file.

9. Account:

- What It Does: Shows information related to your Microsoft account and subscription, such as your account name, options for switching accounts, and managing your subscription.

- Example: You can use this option to sign out of your account or check your subscription status.

10. Options:

- What It Does: Opens Excel's settings menu, where you can customize various program settings and preferences.

- Example: You can adjust settings like Excel's default font, formula calculation options, or proofing preferences to tailor the program to your needs.

These options in the "File" menu help you manage your Excel workbooks effectively, whether you're starting a new project, saving your work, sharing it with others, or customizing Excel's behavior to suit your preferences.

Chapter 2.8 Undo and Redo functions

Undo and Redo functions in Excel are like having a time machine for your spreadsheet. They allow you to step backward and forward through your actions, making it easy to correct mistakes or revisit previous changes. Here's how Undo and Redo work in Excel:

Undo Function:

- What It Does: The Undo function lets you reverse the last action you performed in Excel.

How to Use Undo

1. Ribbon Method:
Figure 9

The Undo button in Excel is like a magical time machine for your spreadsheet. It lets you reverse the last action you took. Here's how you can use it:

1. Find the Undo Button: First, look for the Undo button on the Excel Quick Access Toolbar. It's a curved arrow pointing backward, like a U-turn sign on the road.

2. Click the Undo Button: Click on that Undo button. It's like pressing a 'Rewind' button for your last action.
3. See the Magic: After clicking Undo, you'll notice your last action gets reversed. It's as if it never happened.

Remember, you can keep clicking Undo to step back through several actions, but it can't go back forever. So, it's a handy tool when you make a mistake and need to take a step back.
2. Keyboard Shortcut: You can also use the keyboard shortcut "Ctrl+Z" (hold down the Ctrl key and press Z) to quickly undo the last action.

Example: Let's say you accidentally deleted a cell containing important data. You can undo this action by clicking the "Undo" button or pressing "Ctrl+Z," and the deleted cell will reappear with its original content.

Redo Function:

- What It Does: The Redo function allows you to redo actions that you've previously undone. It's like going back in time and then deciding to move forward again.

How to Use Redo:
Figure 10

The Redo button in Excel is like your trusty 'Forward' button. It helps you reapply an action that you've just undone using the Undo button. Here's how to make the most of it:

1. Locate the Redo Button: The Redo button is usually right next to the Undo button, and it's represented by a curved arrow pointing forward. It's like saying, "I want to go forward again!"

2. Click the Redo Button: After you've undone an action with the Undo button, click the Redo button. It's like taking a step forward in time, reapplying the last action you undid.

3. See the Magic Again: Just like that, your last undone action is back. It's as if you're rewinding your undo.

The Redo button is super handy when you change your mind about an action and want to bring it back. So, it's your 'do-over' button in Excel. 2. Keyboard Shortcut: You can use the keyboard shortcut "Ctrl+Y" (hold down the Ctrl key and press Y) to quickly redo the last undone action.

Example: After undoing the accidental deletion of a cell, you realize that you didn't want to undo it. You can redo the action by clicking the "Redo" button or pressing "Ctrl+Y," and the cell will be deleted again.

Undo and Redo are like safety nets for your Excel work. They allow you to experiment and make changes with the confidence that you can always go back or forward in time to get the results you want.

Chapter 2.9 Saving Excel Data

Saving your Excel data is as simple as a few clicks. It's important to save your work frequently to avoid losing any changes. Here's how you can do it: Using the Quick Access Toolbar:

1. Locate the Quick Access Toolbar: It's a small toolbar at the very top of your Excel window, usually on the left-hand side.

Figure 11
2. Look for the Floppy Disk Icon: This is the universal symbol for saving. It looks like a small square with a pencil.

3. Click the Floppy Disk Icon: One click, and your work is saved! If you're saving for the first time, a "Save As" window will pop up, asking you where you want to save your file and what to name it.

4. Name Your File: Type in a name for your Excel file, and choose the location on your computer where you want to save it.
Figure 12
5. Click "Save": Once you've named your file and chosen the location, hit "Save," and you're done.

Using the File Menu:

1. Locate the "File" Tab: This is usually in the top left corner of your Excel window.
2. Click "File": A menu will open on the left.

3. Select "Save" or "Save As": If it's your first time saving the file, choose "Save As" and follow the prompts to name your file and choose the location.

4. Name Your File: Give your file a name and choose a location on your computer to save it.
5. Click "Save": Hit the "Save" button, and your file is safely stored.
Using a Keyboard Shortcut (Ctrl + S):
1. Press Ctrl + S: This keyboard shortcut works from anywhere in Excel. It's like a magic wand – it saves your work instantly!
Example:

Imagine you're working on your monthly budget in Excel, and you've just added your income and expenses. You don't want to lose all this data, so you decide to save your file:

1. Click the little floppy disk icon in the Quick Access Toolbar. 2. Name your file "Monthly_Budget" and choose your "Documents" folder as the location.
3. Click "Save."

Now, your budget is safely stored on your computer. You can continue working, knowing that your changes are saved and secure. Remember, save early and save often to avoid any worries about losing your important work!

Part III: Data Formatting and Styling

Chapter 3.1 - Cut, Copy, Paste, Format Painter and Clipboard

Let's understand the basic functions of Cut, Copy, Paste, and Format Painter in Microsoft Excel with examples:

You can use cut copy paste by clicking on it
or you can use their keyboard shortcuts,
for save our time we will use their shortcuts :

1. Cut (Ctrl+X)
2. Copy (Ctrl+C)
3. Paste (Ctrl+V)
4. Format Painter
5. Clipboard

1. Cut (Ctrl+X):

- Imagine you have a list of items in Excel, and you want to move one item from its current location to another. Cutting is like picking up that item. Example: You have a list of fruits, and you want to move "Banana" from cell A2 to cell B2.
A. Select cell A2, press "Ctrl+X" to cut it.
A. Cut (Ctrl+X) B. Paste (Ctrl+V)
B. Go to cell B2 and press "Ctrl+V" to paste it. Now, "Banana" is in cell B2, and it's removed from cell A2.

2. Copy (Ctrl+C):

- Copying is like making a duplicate of something without removing the original. It's useful when you want to place a copy of the same item in multiple locations.

- Example: You have a formula in cell A1 that calculates a total. You want to use the same formula in cell B1.
A. Select cell A1, press "Ctrl+C" to copy it.
B. Go to cell B1 and press "Ctrl+V" to paste it. Now, you have the same formula in both cells A1 and B1. A. Copy (Ctrl+C)

3. Paste (Ctrl+V):

B. Paste (Ctrl+V)
- Pasting is like placing the item you've cut or copied into a new location.
- Example: After cutting or copying, you use "Ctrl+V" to paste the item into the desired cell.
➢ Note - You can cut or copy multiple cells item and paste it anywhere in excel

4. Format Painter (Format Painter Icon):

The Format Painter is like a magic brush that copies the formatting (e.g., font, color, borders) from one cell and applies it to another.

Example: You have a cell (like A1 – as per below picture) with a specific font style, background color, and border formatting that you want to replicate in other cells.

A. Select the cell with the desired formatting (example A1 Name)
B. Click the "Format Painter" button
(it looks like a little paintbrush) on the toolbar.
C. Now, your cursor will look like a paintbrush
. Click on the cell or select multiple cells, where you want to apply the same formatting. The formatting is copied to the new cells.
A
B

Important :

C

"Unlock the Magic of Format Painter : Did you know that you can supercharge Excel's Format Painter? By simply double-clicking on the Format Painter icon, you can apply your chosen cell formatting across multiple cells and even multiple Excel sheets in a snap. And when you're done, turning off the Format Painter is as easy as pressing the 'Esc' key on your keyboard. It's like having a formatting wizard at your fingertips!"

5. Clipboard Function

Unlocking the Power of the Clipboard in Excel: When we copy or cut anything, whether it's numbers, text, tables, or charts in Microsoft Excel, it's like saving those items in a handy virtual storage space called the clipboard. Think of it as your personal data vault.

Copy-Paste Anywhere, Anytime: Now, here's the magic part! You can paste that saved item or data multiple times, not only within the same Excel sheet but also across different sheets, files, or even other
programs in the Microsoft Office family. It's your data on the go!

Close
How to Paste with the Clipboard: To make use of this, you can go to the "Home" tab in Excel and find the Clipboard icon
.

Clicking it reveals a treasure trove of all the stuff you've cut or copied. Now, simply click on the item you want to paste (see image). But remember, data will paste in your selected cell, like a target.

Temporary Storage: Here's the twist: Your data remains safely stored in the clipboard only as long as you have Excel and other Office programs open. Once you close them all, everything in the clipboard gets cleared. So, be sure to paste your precious data before shutting down!

In a nutshell, the clipboard is your secret helper for moving and copying data in Excel and beyond. Just remember, it's like a fleeting memory—once you close the door, it's gone!

Chapter 3.2 - Formatting Text with Font Group

Let's explore the Font Group functions in Excel.
The Font Group in Excel is like a magical style studio for your text. It lets you get creative with how your words look. Here's what each function does: Figure 13

1. Font Name:

- Use: This is where you pick the style of your text, like "Arial" or "Times New Roman." Clicking on the drop-down list allows you to select the font that you want to use for your text.

- Benefit: Different fonts give your text a unique personality. Use "Comic Sans" for fun or "Calibri" for a professional touch.

2. Font Size:

- Use: Adjusts the height of your text. You can make it big for titles or small for footnotes. Clicking on the drop-down list allows you to select the size of your text in points (minimum font size 8 and maximum font size 72).
- Benefit: It's like choosing the right font size in a book to make headings pop and the main text easy to read.

3. Increase Font Size:

- Use: Make your text larger to grab attention.
- Benefit: When you want specific text to stand out, like headings or titles. It's like making a sign with bigger letters.

4. Decrease Font Size:

- Use: Shrink your text to fit more in a small space.
- Benefit: Handy for squeezing detailed information into a limited area, like footnotes. It's like making text smaller on a busy sign.

5. Bold:

- Use: Click to make your text thicker and stand out.
- Benefit: It's like putting a spotlight on important words. "Bold" says, "Hey, look at me!"

6. Italic:

- Use: Click to Italicizes your text for a stylish, slanted look.
- Benefit: Use it when you want to add a touch of elegance or emphasis to your words.

7. Underline:

- Use: Underline makes your text more noticeable by

adding a line below it.
Single Underline: Click the "Underline" button for a single line. Double Underline: Use the dropdown for a double underline.

- Benefit: It's like drawing attention to key points. Use it for links or important terms.

8. Border:

- Use: Add lines or borders around cells to create boundaries or highlight sections. (Click on the dropdown to access all border settings like border style, color, thickness etc.)

- Benefit: Borders help you structure your data, making it easier to read and understand. You can use them to create tables, grids, or emphasize important data.

9. Fill Color:

- Use: It's like giving cells a colorful background. Click the button, choose a color from the dropdown, and the selected cells will be filled with that color..

- Benefit: Enhances data visibility, simplifies categorization, and improves spreadsheet readability.

10. Font Color:

- Use: Choose a color for your text, making it stand out (Click the button, and a dropdown with color options appears).
- Benefit: It's like adding a splash of color to your words, helping important information pop.

11. Font Settings

A. Strikethrough:

- Use: Puts a line through your text to show it's no longer relevant, like this Demo Not Required.

(select the text or value from formula bar or by double clicking on the cell and click on Strikethrough to apply the formatting).

- Benefit: Great for marking items as completed or canceled.
Figure 14
B. Subscript and Superscript:
- Use: Subscript makes text smaller and places it below the line (e.g., H2O). Superscript makes text smaller and places it above the line (e.g., x²).

(select the text or value from formula bar or by double clicking on the cell and click on Subscript or Superscript to apply the formatting as you needed).

- Benefit: Perfect for scientific notations, math formulas, or chemical symbols.

So, think of the Font Group in Excel as your text's fashion designer. You can choose fonts, set borders on cell, make text or value big or small, add emphasis, and even play with colors to make your spreadsheet not just informative but also visually appealing!

Chapter 3.3 - Aligning Data with Alignment Group

Let's explore how to use the Alignment Group in Excel with detailed explanations for each feature:

Alignment Group - Making Your Text Look Great

Imagine the Alignment Group in Excel as your personal text stylist. It helps you organize and position text within cells to create a polished and readable spreadsheet. Here's how to use each feature:

Figure 15

1. Vertical Alignment:

- Usage: Select the cells with text, go to the Alignment group, and pick top, middle, or bottom alignment.
- Benefit: Imagine arranging objects on a shelf. Top for headings, middle for content, and bottom for footnotes.
Figure 16

2. Horizontal Alignment:

- Usage: Select the cell or cells with text, go to the Home tab, and in the Alignment group, choose left, center, or right alignment.
- Benefit: It's like aligning text on a page. Left align for labels, center for titles, and right for numbers.
Figure 17

3. Orientation:

- Usage: Select the cell with text, open the Orientation dropdown in the Alignment group, and choose the desired angle.

- Benefit: Ideal for fitting text into tight spaces or adding a creative touch to your spreadsheet. This tool is for tilting your text. You can make it slant, stand tall, or rotate in different ways. It's like placing a sign at a funky angle to catch people's attention.

Figure 18
Note - You can use other orientation
option by just clicking on it

4. Increase/Decrease Indent:

- Usage: Select the cell or cells with text, click Increase Indent or Decrease Indent in the Alignment group. Increase Indent nudges your text one step to the right, creating a bit of space between the content and the cell's left edge. If you've indented too much and want to bring it back to the left, use Decrease Indent to shift it back closer to the cell's left edge.

- Benefit: Increase/Decrease Indent is like changing margins in a book. Use it to neatly organize text by pushing it to the right (Increase) or bringing it back to the left (Decrease). This helps create a hierarchy for your data, making it visually appealing, clear, and well-structured.

Figure 19

5. Wrap Text:

- Usage: Select the cell with text, click Wrap Text in the Alignment group.

- Benefit: Useful for long text or creating a neat layout. It automatically adjusts the cell's height to fit the content. If you have a long message, this tool helps your text wrap onto the next line within the same cell, so it all fits neatly.

Before Cell A1
After Clicking on Wrap Text Cell A1

6. Merge & Center:

- Usage: Select the cells you want to merge, click Merge & Center in the Alignment group.
- Benefit: Perfect for creating headers or titles that span multiple cells. It centers the text both horizontally and vertically.

Example - Let's say you have 'Company Employee Data' in cell A1, and you want it to span cells A1 to C1. To do this, select these three cells, then click 'Merge & Center.' Your text 'Company Employee Data' will now be centered and fill cells A1 to C1 as a single, eye-catching header or title.

Figure 21

7. Merge Across & Merge Cells and Unmerge Cells:

You can access these option by clicking dropdown.

A. Merge Cells: This option merges all the selected cells into a single, larger cell. It doesn't pay attention to rows or columns; it just combines all the cells into one. It's great for creating a single-cell header or title that spans across rows and columns.

Before
After

B. Merge Across : When you use this option, you can select a range of cells and choose to merge them across rows or columns, meaning it will merge cells in each row or column separately without affecting the adjacent rows or columns. This is useful when you want to create a multi-row or multicolumn header, but you don't want the merged cells to expand beyond a specific row or column.

Before Figure 23 After

C. Unmerge Cells:

- Usage: Select merged cells, click Unmerge Cells in the Alignment group.
- Benefit: Reverts merged cells back to individual ones if you need to separate them.
Before (Merged Cells) Figure 24After (Unmerged Cells)

8. Alignement Setting

- Usage: Click the Text Control button in the Alignment group to access advanced options for text direction, vertical alignment, and text flow.

- Benefit: Offers precise control over text formatting within cells. A. Text Control: This is like setting the direction and orientation of your text. You can rotate it, flip it, or make it look diagonal. It's like changing the way a road sign points - it can go left, right, or any direction you choose.

Figure 25

B. Shrink to Fit:

- Usage: Select the cell with text, click Shrink to Fit in the Alignment group.

- Benefit: If your text is too big for the cell, this tool
can make it smaller so it fits. It's like magically
resizing your text to fit in a smaller space. Handy for

making text fit without changing the row's height.

Excel automatically reduces the font size to fit the
content.

Before Figure 26 After Clicking on
Shrink to Fit

C. Center Across Selection:

- Usage: Select cells, click Center Across Selection in the Alignment group.
- Benefit: It's used to make a title or header span across multiple columns without merging the cells into one.
Before
After Selecting Center Across Selection Figure 27

By using these features in the Alignment Group, you'll have the tools to arrange your text precisely the way you want in Excel, making your spreadsheets not only organized but also visually appealing. It's like having a professional designer at your fingertips for your data!

Chapter 3.4 - Manage Numbers with Number Group

Number Group - Shaping Your Data

The Number Group in Excel acts as your data sculptor, allowing you to format and manipulate numbers to present your information clearly. Here's how to use it and the use and benefits of each function, complete with examples:

Figure 28

1. Number Formats:

- Usage: Select the cell or cells containing numbers. In the Number group on the Home tab, choose from various number formats like General, Number, Currency, Date, and more.

- Benefit: Helps in presenting numerical data in a more understandable and visually appealing manner. For instance, formatting a

number as currency makes it look like $1000. Figure 29

2. Accounting Number Format:

- Usage: Select the cells with currency values,
click the Accounting Number Format button in the
Number group.

- Benefit: Makes your financial data look
professional, organized, and easier to read. You
can also add various currency symbols to your

values from the drop-down icon. For example,

you can display $1000 or ₹1000 depending on
your currency choice.

Figure 30

3. Percent Style (%) :

- Usage: Highlight the cell or cells with numbers, click the Percent Style button in the Number group.
- Benefit: Easily convert numbers into percentages. For example, change "0.15" to "15%. Figure 31

Important If you plan to input percentage values into multiple cells, it's a good practice to format the entire range as a percentage before doing so. This makes calculations and data entry more straightforward and accurate.

3. Comma Style:

- Usage: Select cells containing large numbers, click the Comma Style button in the Number group.
- Benefit: Add thousands separators for improved readability. For instance, change "1000000" to "1,000,000.

4. Increase Decimal and Decrease Decimal:

- Usage: Highlight the cells with numbers, click Increase Decimal to add decimal places, and Decrease Decimal to reduce them.
- Benefit: Precisely control the level of detail in decimal numbers. For example, modify "3.14159265" to "3.14" or "3.1416.
Figure 33

5. Short Date Format:

- Usage: Highlight cells with dates, use the
dropdown in the Number group to select Short

Date formats.

- Benefit: Short Date format displays the date in a
concise manner, typically showing only the numerical day,
month, and year without any additional text. It's

commonly used for data analysis, sorting, and filtering.

For example, if you have a date like "15-10-2023" or long
date (Sunday, October 15, 2023) in your spreadsheet,
using the Short Date format will display it as 15-10-23.

6. Long Date Format :

- Usage: Highlight cells with dates, use the
dropdown in the Number group to select Long

Date formats.

- Benefit: Long Date format displays the date with
the complete month name, day of the week, and the
year. It provides a more descriptive representation of

the date. It makes your data more comprehensible and
is especially useful when sharing information with
others who may not be familiar with numerical date
formats.

For example, if you have a date like "15-10-2023" or
short date “15-10-23” in your spreadsheet, using the
Long Date format will display it as (Sunday, October

15, 2023)

Figure 35

7. Custom Date Formats :

Using custom date formats in Excel allows you to format dates exactly as you need them. Here's a step-by-step guide on how to use custom date formats with an example:

Step 1: Select the Cells with Dates
• Begin by selecting the cells containing the dates that you want to format.
Step 2: Access the Format Cells Dialog Box
You can do this in two ways:
• Right-click on the selected cells and choose "Format Cells."

• Alternatively, go to the "Home" tab in the Excel ribbon, find the "Number" group, and click the small arrow in the bottom right corner of the group. This opens the "Format Cells" dialog box.

Step 3: Choose the "Number" Tab
• In the "Format Cells" dialog box, select the "Number" tab at the top.
Step 4: Select "Custom" Category

• On the left side of the dialog box, you'll see a list of different categories. Choose "Custom" from this list.
Step 5: Define Your Custom Date Format
• In the " Type" box on the right side, you can create your custom date format. Here are some common date format codes:

• "dd/mm/yyyy" for day/month/year
• "mm/dd/yyyy" for month/day/year
• "dddd, mmmm d, yyyy" for a long date format
• "d-mmm-yy" for a short date format

(Here, "dd" represents the day, "mmm" represents the three-letter abbreviation of the month, and "yy" or "yyyy" represents the year) Step 6: See a Preview

• As you enter the format in the "Type" box, you'll see a preview of how your selected date will appear in the "Sample" box below. This gives you an idea of how your date will look with the chosen format.

Step 7: Apply Your Custom Format

• Once you've defined your custom date format and are satisfied with the preview, click the "OK" button. The selected cells will now display the dates in the format you specified.

Figure 36

Example: Imagine you have a date in cell A1, like "10/15/2023," but you prefer it to be displayed as "15-Oct-23." Using the method described, you can easily transform the date format. After doing so, cell A1 will now show the date in the format you wanted, which is "15-Oct-23," thanks to your custom date format settings (see figure 36).

Important when you're using a custom date format, you can choose to use a dash (-) instead of a slash (/) as the separator between day, month, and year.

Here's a table explaining what each of these date format codes (d, dd, mm, mmm, mmmm, yy, yyy, yyyy) will display in Excel:
Format DescriptionCode

d Day of the month dd Day of the month (zero-padded) m Month (number)

mm Month (number, zero-padded) mmm Abbreviated month name mmmm Full month name

y Year (2 digits) yy Year (2 digits) yyy Year (4 digits) yyyy Year (4 digits)

Example Display 5
05
4
04
Apr
April
22
22
2022
2022

Using custom date formats in Excel gives you flexibility in how you present date information in your spreadsheets, making it more user-friendly and suitable for your specific needs.

9. Custom Time Formats:

To use a custom time format in Excel, follow these steps:
Step 1: Select the cells with time values.
• Click on the cell or cells containing your time values. Make sure they are selected.
Step 2: Open the Format Cells dialog.

• Right-click on the selected cell(s), and choose "Format Cells" from the context menu. You can also press Ctrl + 1 as a keyboard shortcut.

Step 3: Choose the "Number" tab.
• In the "Format Cells" dialog, go to the "Number" tab if you're not already there.

Step 4: Select "Custom" from the Category list.
• In the "Category" list on the left, choose "Custom."
Step 5: Input your custom time format.

• In the "Type" field, you can enter your custom time format using various time format codes. For example, "h:mm AM/PM" will display time in the 12-hour clock format with AM/PM.

Figure 37

Step 6: Click "OK."
• After entering your custom time format, click "OK" to apply it.

Here's an example: Suppose you have a time value in cell A1, which is in the format "13:30:00" (1:30 PM in 24-hour clock format), and you want to display it as "1:30 PM" using a custom time format. Follow the steps above and use the custom format "h:mm AM/PM." After applying the format, you will see cell A1 now displays the time as "1:30 PM" based on your custom time format. (follow above method)

This custom format allows you to format time values exactly as you prefer in your Excel spreadsheet.

8. Fraction Formats:

- Usage: Highlight cells containing fractions,
go to the Number group's dropdown, and select
fraction formats.

- Benefit: Display fractions accurately. For
instance, change "0.5" to "1/2."
Figure 38

9. Scientific Notation:

- Usage: Select cells with scientific numbers, choose the Scientific Number Format from the Number group.
- Benefit: Display large or small numbers in scientific notation for clarity. For example, change "5000000" to "5.00E+06."
Figure 39

10. More Number Formats:

- Usage: Click the "More Number Formats" option in the Number group's dropdown to access advanced number formatting options.
- Benefit: Customize number formatting with precision, including custom formats for specific requirements.

Using the Number Group's functions allows you to make your data more reader-friendly and visually appealing. Just like a sculptor shapes a piece of art, these tools help you present your numbers in the best possible way for your audience.

Chapter 3.5 - Applying Conditional Formatting

Conditional Formatting in Microsoft Excel is a powerful tool that allows you to automatically format cells based on specific conditions or rules you define. It helps you highlight and visualize data, making it easier to spot trends, patterns, and anomalies. Here's how to use Conditional Formatting and an explanation of its options, along with examples

Using Conditional Formatting:

1. Select the Cells: Start by selecting the cells or range of cells that you want to apply conditional formatting to. These are the cells where Excel will evaluate the conditions you set.

2. Access Conditional Formatting: Go to the Home tab in Excel's ribbon. You'll find the Conditional Formatting button in the Styles group. Click on it to access various conditional formatting options.

Conditional Formatting Options and Their Benefits:
1. Highlight Cells Rules:

- Greater Than, Less Than, Between, Equal To, etc .: These options allow you to highlight cells based on numeric conditions. For example, you can highlight all cells with values greater than 100 in a sales report.

How to Highlight Values Greater Than 100 in Excel: (see figure 41)
1. Select Data: Highlight the cells containing your sales data in Excel.

2. Conditional Formatting: Go to the "Home" tab and click on "Conditional Formatting."
3. Choose "Greater Than": Hover over "Highlight Cells Rules" and select "Greater Than."
4. Set Value: In the dialog box, enter "100" as the threshold. 5. Apply Formatting: Choose formatting options, such as font or fill color.
6. Confirm and Apply: Click "OK" to apply the formatting. Now, cells with values greater than 100 will be highlighted, helping you analyze your sales report.(see figure 41)
Example 2 : Highlight all sales figures greater than 7,000 in Green and less than 5,000 in Red for quick performance assessment.
To achieve this, we'll use conditional formatting two times for two different conditions.

For Values Greater Than 7,000 (See Figure 42):
1. Select Data: Highlight the cells with your sales data.

2. Conditional Formatting: Go to the "Home" tab and click "Conditional Formatting."
3. Greater Than: Hover over "Highlight Cells Rules" and select "Greater Than."
4. Set Threshold: Enter "7000" in the dialog box.
5. Apply Green Formatting: Choose "Green Fill with Dark Green Text" for highlighting greater values.
6. Confirm and Apply: Click "OK."

Figure 42
For Values Less Than 5,000 :
1. Select Data: Highlight the cells with your sales data.

2. Conditional Formatting: Go to the "Home" tab and click "Conditional Formatting."
3. Less Than: Hover over "Highlight Cells Rules" and select "Less Than."
4. Set Threshold: Enter "5000" in the dialog box.
5. Apply Red Formatting: Choose "Light Red Fill" for highlighting lesser values.
6. Confirm and Apply: Click "OK."
Now, cells with sales figures meeting these criteria will be highlighted in the chosen colors. This makes performance assessment quick and easy. Figure 43

- Text That Contains:Use this to highlight cells containing specific text

or keywords. For instance, you can highlight all cells containing the word "urgent" in a task list.
Example: Highlight all task descriptions containing the word "urgent" in bold and red for immediate attention.

To highlight text follow these steps:
1. Select Data: Highlight the cells containing your task descriptions.

2. Conditional Formatting: Go to the "Home" tab and click on "Conditional Formatting."
3. Text That Contains: Hover over "Highlight Cells Rules" and select "Text That Contains."
4. Enter Text: In the dialog box, enter "Urgent" (or your keyword).

5. Custom Format: Click the "Custom Format" option from the dropdown menu.

6. Font Tab: In the Format Cells dialog, go to the "Font" tab.
7. Set Bold: Under "Font Style," select "Bold."
8. Choose Red: Click on the color option to select red (or your preferred highlighting color).
9. Confirm and Apply: Click "OK" to confirm the formatting. Now, any task descriptions containing the word "urgent" will appear in bold red, making them stand out for immediate attention.

2. Top/Bottom Rules:

- Top 10 Items, Above Average, Below Average: These options help you identify top or bottom performers within a dataset.
Example: Highlight the top 5 sales value in a list.

3. Data Bars:

• Usage: Data Bars allow you to visually represent the values in a cell with horizontal bars. The length of the bar is proportional to the cell's value.

• Benefit: It helps you quickly compare values and understand their relative sizes.

How to Use Data Bars :
1. Select Your Data: Highlight the range of cells you want to format.

2. Conditional Formatting: Go to the "Home" tab and click "Conditional Formatting."
3. Data Bars: Hover over "Data Bars."

4. Choose a Color: Select a color scheme for your data bars. The color represents the filled portion of the bar.
Example: Suppose you have a list of test scores. By applying Data Bars, higher scores will have longer bars, making it easy to see variations in scores.

Figure 46

4. Color Scales:

• Usage: Color Scales allow you to apply a gradient of colors to cells based on their values. Higher values get one color, and lower values get another color.

• Benefit: It helps in identifying trends and variations in your data. How to Use Color Scales:
1. Select Your Data: Highlight the range of cells you want to format.

2. Conditional Formatting: Go to the "Home" tab and click "Conditional Formatting."
3. Color Scales: Hover over "Color Scales."

4. Choose a Scale: Select a color scale that suits your data. Excel will automatically apply colors based on values.
Example: In a sales report, you can use Color Scales to quickly identify the best and worst performing products. High sales are green, low sales are red, and values in between are shades of yellow.

Figure 47

5. Icon Sets:

• Usage: Icon Sets let you add icons to cells based on their values. You can choose from a variety of icons to represent different conditions.

• Benefit: It provides a clear and visual way to understand your data.

How to Use Icon Sets :
1. Select Your Data: Highlight the range of cells you want to format.

2. Conditional Formatting: Go to the "Home" tab and click "Conditional Formatting."
3. Icon Sets: Hover over "Icon Sets."

4. Choose an Icon Set: Select an icon set that best suits your data. You can choose different icons for different conditions.
5. Example: Imagine you have a sales report with monthly sales figures, and you want to use Icon Sets to visually represent the performance of each month.

Figure 48

4. New Rule:

The "New Rule" feature in Conditional Formatting is like giving your data a personalized set of formatting instructions. You can create custom rules using Excel formulas to format cells based on specific conditions.

Example 1: Prioritizing Tasks

Suppose you have a task list with due dates, and you want to highlight tasks that are overdue. You can create a custom rule as follows:
Figure 49

Step 1: Go to the Home tab in Excel.
Step 2: Click on "Conditional Formatting."

Step 3: Choose "New Rule" to create a custom
rule.

Step 4: In the "New Formatting Rule" dialog

box, select the last option: "Use a formula to
determine which cells to format."

Step 5: In the formula bar, enter the following formula: =TODAY()>B2:B7
• TODAY() is a function that represents the current date.
• > is used to check if a date is greater (in the past) than today's date.
• B2:B7 represents the range containing due dates for your tasks.

Step 6: Choose a Formatting Style: Click on the "Format" button. In the "Format Cells" dialog, go to the "Fill" tab and select a background color, like Yellow, for overdue tasks.

Figure 50

Step 7: Click "OK" to apply the formatting.
Note - When the formula is TRUE, the specified formatting will be applied.

Result: Now, any task with a due date in the past will have a yellow background, making overdue tasks stand out. This helps you quickly identify and prioritize tasks that need attention.

Using "New Rule" in Conditional Formatting gives you the flexibility to apply custom formatting based on your specific data conditions, making your spreadsheets more informative and visually appealing. It's like having your own data designer to create personalized formatting rules.

5. Clear Rules:

- Clear Rules from Selected Cells or Entire Sheet: Sometimes, you may need to remove or clear the conditional formatting rules you've applied to cells or the entire sheet. Here's how you can do it:

Figure 51

Remove Conditional Formatting rules from Selected Cells:
1. Select cells.
2. Go to "Home" > "Conditional Formatting."
3. Choose "Clear Rules from Selected Cells."

Remove Conditional Formatting rules from Entire Sheet:
1. Go to "Home" > "Conditional Formatting."
2. Select "Clear Rules from Entire Sheet."
This lets you start fresh or make data look how you prefer.

6. Manage Rules:

- Edit, Delete, or Prioritize Rules: This option allows you to modify existing rules, delete unnecessary ones, and change their priority if you have multiple rules applied to the same cells.

Example: Adjust the rule that highlights overdue tasks to use a different color for better visibility.
Figure 52

In summary, Conditional Formatting in Excel is a versatile tool for enhancing the visual representation of your data. It allows you to apply formatting based on conditions, making your spreadsheet more visually appealing and informative. Whether you're highlighting important figures, tracking progress, or flagging issues, Conditional Formatting options can help you interpret data at a glance and make better decisions.

Chapter 3.6 - Formatting Data as Tables

"Format as Table" is a feature in Excel that allows you to quickly apply a predefined table style to a range of data. It's like giving your data a visual makeover for better organization and readability.

How to Use " Format as Table":

1. Click Anywhere: Simply click anywhere within your data, and Excel will automatically detect and select the data range you want to format as a table.

Figure 53

2. Access "Format as Table": Go to the Home
tab in Excel's ribbon, locate the "Format as Table"
button in the Styles group, and click on it.

3. Choose a Table Style: Excel will display a
gallery of table styles to choose from. Select the
one that suits your data best.

4. Confirm Your Selection: Ensure that the "My table has headers" option is checked if your data has headers. Excel will use these headers for column names.

5. Click OK: Click the OK button to apply the selected table style.
Figure 55
Benefits of "Format as Table":

- Enhanced Readability: Table styles improve data readability by adding alternating row colors and clear header formatting, making it easier to follow and understand.

- Easy Filtering and Sorting: When you format your data as a table, Excel automatically adds filter buttons to the headers, simplifying data analysis. You can quickly sort and filter data without complex commands.

- Dynamic Range: Tables expand automatically as you add new data rows, ensuring that your formatting stays consistent.

Chapter 3.7 - Utilizing Cell Styles

"Cell Styles" in Excel allows you to apply a set of formatting options to selected cells or ranges, creating a consistent look for your data. It's like dressing up your data cells in a variety of outfits.

How to Use "Cell Styles":
1. Select Cells: Start by selecting the cells or range of cells that you want to format with a specific style.
2. Access "Cell Styles": Go to the Home tab in Excel's ribbon, locate the "Cell Styles" button in the Styles group, and click on it.
3. Choose a Cell Style: Excel offers a gallery of predefined cell styles categorized by themes. Select the style that suits your data. 4. Apply the Style: Click on the style to apply it to the selected cells.
Figure 57
Benefits of "Cell Styles":
- Consistency: Cell styles help maintain a consistent appearance throughout your spreadsheet, making it look more professional.
- Quick Formatting: Applying a cell style is faster and easier than manually adjusting individual formatting settings.
- Ready-Made Themes: Excel provides a range of themes and styles to choose from, ensuring that your data matches your preferred theme.

In summary, "Format as Table" and "Cell Styles" are two valuable formatting tools in Excel that help you make your data visually appealing, organized, and consistent. These features make your spreadsheets more reader-friendly and efficient for data analysis.

Chapter 3.8 Dealing with Large Numbers

You can input large numbers in Excel by either using a single quote (') before the number or by converting the cell to text format. However, please note that when a number is in text format, you can't perform calculations with it. Let's explain this with a table and an example:

Entering Large Numbers in Excel:

Excel is designed to handle a wide range of data, including large numbers. Here are two methods to input large numbers:
Method 1: Using a Single Quote ('):

1. Simply put a single quote (') before entering the large number in a cell.

Example:
Suppose you want to enter the number 123456789012345 in cell A1. You

can input it as '123456789012345.

Figure 58
Figure 59
Method 2: Converting the Cell to Text Format:
1. Select the cell or cells where you want to input large numbers.
Figure 60
2. Go to the "Number Format" option in the Excel ribbon.
3. Choose "Text" from the list of formatting options. This will convert the selected cells to text format.
Now, you can enter large numbers without needing the single quote.

Important Note: If you have large numbers formatted as text in a cell, you won't be able to perform calculations with them because Excel sees them as text, not numbers.

To do calculations, you need to change the cell format back to a numeric format. However, keep in mind that just changing the cell format won't automatically transform the text into numbers. You'll have to delete the existing text and re-enter the values.

Chapter 3.9 Paste Special Options

The "Paste Special" feature provides you with flexibility in handling copied data and is particularly useful when you need to perform specific tasks like pasting values, formatting, or comments in Excel.

Here's a step-by-step explanation of how to use it:
How to Use "Paste Special" in Excel:
Step 1: Copy the Data

First, you need to copy the data that you want to paste in a special way. You can do this by selecting the cell or range of cells, right-clicking, and choosing "Copy" or by pressing Ctrl + C on your keyboard.

Figure 61
Step 2: Select the Destination

Next, select the destination where you want to paste
the data. Make sure the destination cells are of the

same size as the copied data or larger.

Step 3: Access "Paste Special"

• Right-click on the cell where you want to
paste the data.
• Choose 'Paste Special' to see different paste

options.

Step 4: Choose a "Paste Special" Option
In the "Paste Special" submenu, you'll find a variety of paste options:
• All: This pastes everything (including formatting, formulas, and values).
• Formulas: This pastes only the formulas, excluding formatting or values.

• Values: This pastes only the values, excluding formulas or formatting.
• Formats: This pastes only the formatting (like fonts, colors, and borders).

• Comments: This pastes comments from the copied cells.
• Transpose: This switches rows to columns and columns to rows.

Select the desired "Paste Special" option based on what you want to achieve.
For example, if you want to paste only the values, choose Values.
Step 5: Click "OK"
After selecting your option, click OK.
Figure 62

Now, only the values from the original table are pasted into the destination cell. This is helpful when you want to copy data without including formulas or formatting.

Paste Value and it’s formatting

To paste both the value and its formatting from Excel
data, follow these simple steps:
1. Copy your data by selecting the cell or range of
cells and pressing Ctrl + C on your keyboard.
2. Click on the cell where you want to paste your
data. For example A1
3. Go to the "Home" tab located at the top.
4. Click on the dropdown icon next to the
"Paste" option.

5. Select "Values and Source Formatting" from
the menu. Alternatively, you can press the "E"
key on your keyboard.

Now, you'll see the same data pasted in your desired cell, retaining both the value and its original formatting. It's that easy!
Figure 63

Note: Sometimes, if you copy your Excel data and paste it into another cell or sheet, you may face errors like #REF! To avoid these errors or any kind of issues, you can paste your data as values from the "Paste Special" feature.

Chapter 3.10 Creating Headers and Footers

How to Insert Headers and Footers in Excel:
Step 1: Open Your Excel Workbook

Open your Excel workbook that contains the worksheet where you want to insert or delete headers and footers.
Step 2: Go to the "Insert" Tab
Click on the "Insert" tab in the Excel ribbon at the top of the window.
Step 3: Access the Header & Footer Area

In the " Insert" tab, locate the "Text" group. You'll find the "Header & Footer" option there. Click on "Header & Footer." This will open the header and footer area for your worksheet.

Step 4: Insert a Header or Footer

• To insert a header, click on the "Header" section (top margin) in the worksheet. You can now type your desired header text.
• To insert a footer, click on the "Footer" section (bottom margin) in the worksheet. You can type your desired footer text in this area.
Figure 64
Step 5: Customize Headers and Footers

You can customize your headers and footers by inserting various predefined elements like page numbers, current date, file name, etc. To do this, use the options in the "Header & Footer Elements" group on the "Design" tab, which will appear when you're in the header or footer area (click on the header and footer area to see Design or Header & Footer Tab).

Figure 65

• To insert a page number, click "Page Number."
• To insert the current date, click "Current Date."
• To insert the current time, click "Current Time."
• To insert the file name, click "File Name."
• To insert the worksheet name, click "Sheet Name."

Step 6: Close the Header & Footer Area : Once you've inserted or edited your header or footer text and elements, click anywhere outside the header or footer area. This will close the header and footer editing mode.

Step 7: View Your Headers and Footers
After you've added or edited headers and footers in Excel, you may want to see how they will appear when you print your document. To do this: 1. Simply press the " Ctrl + P" button on your keyboard.

How to Delete a Header or Footer:

To delete a header or footer, follow these steps:
Step 1: Access the Header & Footer Area

In the " Insert" tab, locate the "Text" group. You'll find the "Header & Footer" option there. Click on "Header & Footer." This will open the header and footer area for your worksheet.

Steps 2 : Delete Header and Footer
In the header or footer area, select the text or elements you want to delete and press the " Delete" key on your keyboard.
Your header or footer text or elements will be removed.

Note However, once you've reviewed your headers and footers, don't forget to return to the normal view mode:

To switch back to the regular view, look for the "Normal" option at the bottom of the Excel window (see figure 66) , or click on the "View" tab.

Figure 66

Chapter 3.11 Inserting and Managing Comments

Adding or deleting comments in Excel can be a helpful way to provide or remove additional information about specific cells. Here's a step-by-step guide on how to insert and delete comments:

To Insert a Comment:
1. Open your Excel worksheet.

2. Click on the cell where you want to add a
comment. This will be the cell you're

providing additional information about.

3. Go to the "Review" tab in the Excel ribbon. This tab contains all the comment-related features.
Figure 67
4. In the "Review" tab, you'll find a " New Comment" button. Click on it.
5. A comment box will appear next to the cell. You can start typing your comment in this box.
6. To save the comment, press "Ctrl + Enter" button on your keyboard or click on post icon
To Delete a Comment:
1. Open your Excel worksheet containing the comments you want to delete.
2. Click on the cell with the comment you wish to delete. 3. In the " Review" tab, locate the " Delete" button (usually labeled "Delete" or "Delete Comment"). Click on it.
4. The comment associated with the selected cell will be deleted.
Figure 68
Comment Removed from the cell

Repeat these steps as needed to add or delete comments in different cells of your Excel worksheet. This allows you to provide explanations or additional information where required or remove comments that are no longer needed.

Use Mouse to Add or Remove Comments :

Adding or removing comments in Excel is simple. Just follow these steps:
1. Right-click on the cell where you want to include extra information.
2. Choose " New Comment" to add a comment to that cell.

3. If you want to delete a comment, right-click on the cell with the comment and select "Delete Comment."

Chapter 3.12 Go To Special Options

In Microsoft Excel, "Go To Special" is a powerful feature that allows you to select specific types of cells based on various criteria. Here's how to use it step by step:

Step 1: Select Your Cells: Start by selecting the range
of cells, where you want to apply your special
selection.

Step 2 : Go to the "Home" Tab: Click on the
"Home" tab in Excel.
Step 3: Choose "Find & Select": Click "Find &
Select" in the "Editing" group.
Step 4: Select "Go To Special": From the dropdown,
pick "Go To Special."
Step 5: "Go To Special" Dialog Box: A dialog box willFigure 69 appear with several options. Let's explore each option:
Figure 70
1. Notes: This select all the notes in the sheet.
2. Constants: Choose this to select cells with constant values (text or numbers), but not formulas. Handy for pinpointing hard-coded data. 3. Formulas: This helps you select cells containing formulas. Ideal for checking or editing your calculations.
4. Blanks: This selects all the empty (blank) cells in your chosen range. Great for finding missing data.
5. Current Region: Selects the current data region around your active cell. Useful for working within a block of data.
6. Current Array: Picks the current array in multi-cell ranges. Useful for working with array formulas.
7. Objects: Selects objects like charts or images within your data range.
8. Row Differences: Highlights rows where at least one cell differs from the rest of the row.
9. Column Differences: Highlights columns where at least one cell differs from the rest of the column.
10. Precedents: Selects all the cells that provide input to the formulas in your current selection. Helps you understand formula dependencies. 11. Dependents: Highlights cells that depend on the values in the selected cells. Useful for understanding the impact of changes. 12. Last Cell: Selects the last cell in your worksheet, which contains data or formatting.
13. Visible Cells Only: Picks cells that are currently visible based on filters or outlines you've applied.
14. Conditional Formats: Selects cells with conditional formatting rules.
15. Data Validation: Highlights cells with data validation rules like drop-down lists.
Step 6: Choose Your Desired Option:
• After selecting the appropriate option based on your needs, click "OK."
Step 7: Selected Cells:

• Excel will instantly select the cells that match your criteria. You can now perform operations or formatting specific to the selected cell type.

Here's a simple example:
Let's say you have a dataset with numbers, formulas, and blank cells like below and you want to highlight all the cells with formulas in the range.
Figure 71

You would follow the steps above, selecting the " Formulas" option in the "Go To Special" dialog box. Excel will then highlight the cells containing formulas, making them easy to identify and work with.

Scenario 1:You have a spreadsheet in Excel with six months of sales data

for some salesmen, spanning from April to September. However, some salesmen didn't provide data for certain months, leaving Excel cells empty. You want to fill these blank cells with "N/A" (Not Available) and highlight them with a blue color. Here's how you can do it:

Figure 72
Step 1: Start by selecting your entire data range,
including the blank cells.
Step 2: Next, go to the "Home" tab on the Excel ribbon.
Step 3: Click on the "Find & Select" option.
Step 4: From the dropdown menu, choose "Go To
Special."
In the "Go To Special" dialog box:
Step 5: Select the "Blanks" option and click OK.

At this point, all the empty cells within your data will be
selected. You don't need to click anywhere else on
your Excel sheet.

Step 6: Now, start typing your text "N/A" (or your preferred text) either directly into the cells or by using the formula bar for this purpose.
Figure 73
Step 7: After typing, press "Ctrl + Enter" simultaneously on your keyboard.
Now, all the previously blank cells will contain "N/A."
Step 8: To highlight the cells with the newly
added text "N/A," go to the "Home" tab.
Remember - You don't need to click anywhere
else on your Excel sheet.

Step 9: From the "Font" group, select the " Fill
Color" option and pick your desired color from
the dropdown menu (e.g., blue).

That's it! Your cells will now be filled with the text "N/A" and highlighted in the chosen color blue.

Figure 74

Scenario 2:You have an Excel spreadsheet with some blank rows that you

want to remove. Here's how you can do it:
Figure 75
Step 1: Start by selecting your entire data range, including the blank rows you want to delete.

Step 2: With your data selected, go to the "Home" tab in the Excel ribbon. Step 3: Click on the "Find & Select" option.
Step 4: From the dropdown menu, select "Go To Special."
Inside the "Go To Special" dialog box:
Step 5: Choose the "Blanks" option and click OK.

Now, all the blank rows in your data will be selected. No need to click elsewhere on your Excel sheet.

Step 6: Once the blank rows are selected, press "Ctrl+-(minus)" on your

keyboard. This keyboard shortcut is used to delete rows.
Figure 76
Step 7: A dialog box will appear asking you to confirm your selection. Choose " Entire row" and click OK.
Now you'll notice that the blank rows have been successfully deleted from your data.
This method makes it easy to clean up your data by removing unnecessary blank rows, helping you keep your spreadsheet neat and organized.

Chapter 3.13 Using the View Tab

Here's a detailed explanation of the options in the "View" tab in Excel and their uses and benefits :
Figure 77

Workbook Views Group

1. Normal: This is the default view and is best for everyday editing and formatting. It shows the worksheet as you would expect it.

• Use and Benefits: This is the standard view where you can perform your regular data entry, editing, and formatting tasks.

2. Page Break Preview: This view allows you to see where page breaks occur and makes it easier to adjust them.

• Use and Benefits: Useful for understanding and modifying how your data will be divided across multiple pages when printed. It allows you to insert, move, or remove page breaks.

3. Page Layout: In this view, your worksheet is displayed as it would appear on a printed page. It helps you visualize how your content will fit on paper.

• Use and Benefits: Use this when you want to see how your content will look when printed. It helps you adjust margins, headers, footers, and page breaks.

4. Custom Views: This feature lets you save different settings for your worksheets, like print settings and hidden rows/columns, and switch between them easily.

• Use and Benefits: If you have different views or settings you often switch between (e.g., a view for printing and a view for data entry), this helps you quickly apply those settings.

Navigation and Show Group

1. Navigation : The Navigation option in the Excel View Tab helps you quickly find and access specific data, charts, tables, and objects within your workbook.

2. Show: This option lets you toggle gridlines, headings, and the Formula Bar on or off.

• Use and Benefits: You can use this to customize your view. Turning off gridlines, for instance, can make your sheet look cleaner for presentations, while turning off headings can save space. Turning off the Formula Bar can provide more space for your data.

Figure 78

Zoom

You can zoom in or out of your worksheet to make it easier to read and work with.

• Use and Benefits: Helps you to adjust the size of your data on the screen. Useful when you're working with very large or very small datasets.

Figure 79

Winodws Group

1. New Window: It opens a new window with the same workbook, which is useful for viewing different parts of the same worksheet at the same time.

• Use and Benefits: Allows you to work on different parts of your workbook simultaneously, making it easier to reference or compare data.

Figure 80
2. Arrange All: This option is handy when you have multiple workbooks open. It allows you to arrange their windows side by side.
• Use and Benefits: Useful for comparing and working with multiple workbooks simultaneously.

3. Freeze Panes: This feature enables you to lock specific rows and columns so they're always visible, even when you scroll through a large worksheet.
• Use and Benefits: Useful when dealing with large datasets, as it

keeps your headers or important data in view while scrolling.
4. Split: Splitting your worksheet into panes allows you to view multiple sections at the same time. You can scroll each pane independently.
• Use and Benefits: When you need to compare data from different parts of a large worksheet, the "Split" option is very helpful. Each of these "View" tab options provides different ways to work with and view your Excel data, catering to various tasks and preferences.
5. Hide

Use and Benefits: The Hide option allows you to temporarily conceal the current Excel window, providing a clutter-free workspace to focus on your current task. It's useful when managing multiple open workbooks and maintaining a distraction-free environment.

6. Unhide

Use and Benefits: Unhide is used to reveal and restore hidden Excel windows or workbooks. It simplifies the process of accessing previously hidden content, making it easier to switch between different parts of your project or retrieve important data.

Chapter 3.14 Freeze Panes

The Freeze Panes function allows you to lock specific rows or columns in place while scrolling through your Excel sheet, making it easier to view and analyze data. Here's what each option of the freeze panes function does:

1. Freeze the Top Row:

- Open your Excel worksheet.
- Go to the "View" tab on the Excel ribbon.

- In the "Window" group, click on "Freeze
Panes."
- Select "Freeze Top Row."
Figure 81
Now, the top row will remain visible as you scroll vertically.

Benefit : Suppose you have a large dataset with headers in the first row and want to keep those headers visible as you scroll down or across the sheet. By freezing the top row, you can easily refer to column names even when you're far down the sheet.

2. Freeze the First Column:

- Open your Excel worksheet.

- Go to the " View" tab on the Excel ribbon.
- In the "Window" group, click on "Freeze Panes."
- Select "Freeze First Column."

Now, the first column will remain visible as you scroll horizontally. Benefit : Freezing the first column is helpful when dealing with datasets where you want to keep the leftmost column (e.g., item names or IDs) visible while scrolling to the right.

3. Freeze Multiple Rows and Columns:

- Open your Excel worksheet.

- Click on the cell below and to the right of the rows and columns you want to freeze (e.g., if you want to freeze the first row and the first column, click on the cell in the second row and second column (Cell B2).

Figure 82

- Go to the " View" tab on the Excel ribbon.
- In the "Window" group, click on "Freeze Panes."
- Select "Freeze Panes."

Now, the selected first row and column will remain visible as you scroll both vertically and horizontally.
Freezing multiple rows and columns is useful for maintaining a clear view of both row and column headers in large tables.
Here is the Excel Table image of freezing panes first row and first columns Figure 83

4. Unfreeze Panes:

- Open your Excel worksheet.
- Go to the "View" tab on the Excel ribbon.

- In the "Window" group, click on "Freeze
Panes."

- Select "Unfreeze Panes."
This will remove the frozen panes, allowing your worksheet to scroll freely.
Note - Remember that Freeze Panes is an excellent tool for data analysis and can save you time when working with extensive Excel sheets. Shortcut for Freeze/Unfreeze Panes – Alt, W, F, F (press key one by one)

Part IV: Working with Data

Chapter 4.1 Sorting Data

Sorting data in Excel is like organizing a messy pile of documents into neat stacks. It helps you arrange your data in a meaningful order. Here's how to do it :

Scenario: Imagine you have data in Excel with columns A to D. Column A contains item names, Column B has Quantity, Column C holds the rate, and Column D shows the amount.

1. Sorting Text/Item Data in Excel:

1. Select Your Data:

• Action: Click on any cell in the column you want to sort. For example, to sort items in Column A, click on any cell in that column.

2. Access the Sort Menu:
• Location: Go to the "Home" tab in Excel.
• Find the Button: Look for the "Sort & Filter" button, resembling a funnel icon.
• Dropdown Arrow: Click the arrow next to it to open sorting options.
3. Choose How to Sort:

• Ascending Order (A to Z): Pick "Sort A to Z" to arrange data in ascending order. It organizes names from A to Z.

• Descending Order (Z to A): Opt for "Sort Z to A" to arrange data in descending order, sorting names from Z to A.
Ascending Order (A to Z)
Descending Order (Z to A)

2. Sorting Numeric Data:

1. Select Your Data:

• Action: Click on any cell in the column you want to sort. For example, to sort amounts in Column D, click on any cell in that column.

Figure 86
2. Access the Sort Menu:
• Location: Navigate to the "Home"
tab in Excel.
• Find the Button: Look for the "Sort
& Filter" button.
• Dropdown Arrow: Click the arrow
next to it.
3. Choose How to Sort:
• Sort Smallest to Largest: Select "Sort Smallest to Largest" for ascending order.
• Sort Largest to Smallest: Choose "Sort Largest to Smallest" for descending order.
Amount Sorted (Column D)
in ascending orderFigure 87 Amount Sorted (Column D) in descending order

3. Sort Multiple Columns:

- Custom Sort: If you have data with multiple columns and you want to sort by more than one column, choose "Custom Sort" from the dropdown menu. This allows you to specify the sorting criteria for each column.

Scenario: Imagine you have data in Excel with columns A to D. Column A contains item names, Column B has Quantity, Column C holds the rate, and Column D shows the amount.

Let’s see how to Sort multiple columns using Custom Sort:
1. Select Your Data:
• Action: Click anywhere in your data set. For instance, click any cell within the range of your items.

2. Access the Sort Menu:
• Location: Navigate to the "Home" tab.

• Find the Button: Look for the "Sort & Filter" button (it looks like a funnel).
• Dropdown Arrow: Click the arrow next to it.

3. Choose Custom Sort:
• Option: Select "Custom Sort."

4. Custom Sort Dialog Box:
• Dialog Box Appearance: A window titled "Sort" will pop up.

• Column to Sort By: Choose the column by which you want to sort. If sorting items, select Column A.
• Sort On: Pick the type of data, like values or cell color. Choose "Values" for regular sorting.
• Order: Opt for either ascending or descending order.
• Add Level: If you have multiple sorting criteria, you can add levels to refine the sort.
Figure 88
Example - Sorting by Item Name and Then by Rate:
1. First Level:
• Sort by: Choose Column A (Item).
• Order: Opt for ascending (A to Z) or descending (Z to A) based on your preference.

2. Second Level:
• Add Level: Click this to add another level.
• Sort by: Choose Column C (Rate).
• Order: Decide on ascending or descending order.

3. Apply and See the Magic:
• Action: Click "OK" to apply the custom sort. Result: Your data is now sorted first by Item and then, within each Amount group, by Rate.
Original Data Sorted Data Figure 89
Tip: Custom sorting allows you to tailor the sorting criteria to suit your specific needs, providing a more nuanced arrangement of your data.

Sorting data in Excel is like alphabetizing a library or arranging files in a folder by name. It helps you find information quickly and makes your spreadsheet more organized and easy to read.

Chapter 4.2 Filtering Data

Filtering data in Excel is like putting on a pair of smart glasses that let you see and work with just the information you need. It helps you focus on specific data while temporarily hiding the rest. Let's explore how to do this with complete information:

1. Select Your Data: Click anywhere in your data to apply filter. Excel will automatically detect the surrounding data.

2. Activate the Filter:
- Home Tab: Go to the "Home" tab in Excel's ribbon.

- Filter Button: Look for the " Filter" button. It
usually appears as a funnel icon within the "Editing"
group.

- Click: Click on the "Filter" button to activate the
filter for the selected data range.
3. Filter Options:

- Dropdown Arrows: After activating the filter, you'll see small dropdown arrows appear next to the column headers within your selected data. These arrows indicate that you can filter data in those columns.

Figure 90
- Filter Dialog Box: Clicking on a dropdown arrow opens a dialog box where you can select the criteria you want to filter by.
Figure 91
4. Choose Your Filter Criteria:
- Text Filters: If you're filtering text data (e.g., items), you can choose options like "Equals," "Contains," "Begins With," or "Ends With."
- Number Filters: For numeric data (e.g., amount), you can set conditions such as "Greater Than," "Less Than," "Between," or "Top 10 Items."
- Date Filters: When working with dates (e.g., purchase dates), you can filter by specific date ranges, like "This Week," "Next Month," or "Custom."
Figure 92
Text Filters
Figure 93
Number Filters
Date Filters Figure 94
5. Apply Filters:
- Single Criteria: Select one or more criteria in the filter dialog box and click "OK." Excel will hide rows that don't meet the selected criteria.

- Multiple Criteria: You can apply filters to multiple columns at once, allowing you to fine-tune your data display. For example, you can filter sales data for a specific product category and a particular date range simultaneously.

Examples:

- Filtering Names: Imagine you have a list of customer names in column A. You activate the filter and use a text filter to select "Begins With" the letter "J." Excel will hide all the names that don't start with "J."

- Filtering Sales Data: If you have a table with sales data, you can filter the "Sales Amount" column to show only sales that are "Greater Than" $1,000.

- Filtering by Date: Suppose you have a dataset with order dates. You can filter by a specific date range, like "This Month," to view only orders placed within the current month.

6. Remove Filters:
To remove filters and show all the data again, you can
go back to the filter dropdowns and choose "Filter."

Filtering Monthly Data in Excel: Step-by-Step Guide

Scenario: You have a dataset in Excel with dates, item names, and amounts, and you want to filter data for a specific month.

1. Access Filter Function:
• Action: Click anywhere in your dataset.

• Go to Home Tab: Click on the "Home" tab.

• Find "Sort & Filter": Look for the "Sort & Filter" dropdown icon.

• Activate Filter: Select "Filter."
2. Activate Filter Dropdowns:

• Result: Dropdown arrows appear next to your column headers.
3. Filter Monthly Data:
• Click on Date Column (Column A): Open the dropdown for dates.
• Deselect All: Uncheck "Select All" to clear previous selections.
• Choose Specific Month: Select the month you want, e.g., August.
• Apply Filter: Click "OK."
Figure 95
Figure 96
Result: Your data is now filtered to show only the entries for August 2023. You can also sort items such as Item Name, Party or CustomerName, Amount, and more using the same method.

Clear Filter

let's say you've filtered your Excel data to focus on specific stuff. Now, to go back to seeing everything again, it's super easy:
Figure 97
1. Look for the little filter icon in the column header. It's usually a tiny funnel or something similar.
2. Click on it to open the filter options.
3. You'll see an option like 'Clear Filter' or 'Remove Filter.' Just click on that, and voila! Your data is back to its unfiltered, full glory.
Additional Tips:
• You can filter based on multiple criteria across different columns simultaneously.
• To activate the Filter, simply press 'Ctrl+Shift+L.' Press the same shortcut, 'Ctrl+Shift+L,' again to deactivate the Filter.

Chapter 4.3 Working with Random Numbers

Rand and RandBetween are functions in Excel that generate random numbers. They can be useful for various purposes, including simulations and random sampling. Let's explain both functions :

Rand Function:

The Rand function generates random decimal numbers between 0 and 1. You can use this function to generate random numbers for various purposes.

Syntax:
=RAND()
Example 1: Generating Random Decimals

To generate a random decimal between 0 and 1, simply enter the Rand function in a cell, like this:
After Using Rand Function

Now, whenever you recalculate the worksheet or change any cell, Excel will update the values in cells A2 and B2 with new random decimals between 0 and 1.

RandBetween Function:

The RandBetween function generates random whole numbers between specified minimum and maximum values.

Syntax:
=RANDBETWEEN(bottom, top)
Bottom : The minimum value (inclusive) for the random number. top : The maximum value (inclusive) for the random number.

Example 2: Generating Random Whole Numbers
Suppose you want to generate random whole numbers between 1 and 10. You can use the RandBetween function as follows:
After Using Randbetween Function

Now, every time you recalculate or change any cell in the worksheet, Excel will provide new random whole numbers between 1 and 10 in cells A2 and B2.

Pasting Random Numbers as Values:

As We've mentioned, if you want to prevent random numbers from changing automatically when you input data or recalculate the sheet, you can paste them as values. Here's how:

1. Copy the cell with the random number (e.g., cell A2 or B2).
2. Right-click the destination cell where you want to paste the value.
3. Choose "Paste Values" or "Values" from the paste options.
This action will convert the random number to a fixed value, and it won't change unless you manually edit it.

Copying the Formula for Multiple Cells:

To generate random numbers in multiple cells, you can copy the formula of Rand and RandBetween and paste it in those cells. This way, each cell will have a different random number.

These functions are incredibly useful when you need random data in Excel, whether for modeling, simulations, or any situation where randomness is required. Just keep in mind that the values generated are truly random, so they'll change whenever you recalculate the sheet or enter new data.

Chapter 4.4 Adding Illustrations

Using illustrations in Microsoft Excel can enhance your worksheets by adding visual elements such as pictures, shapes, and SmartArt. Here's a step-by-step explanation with examples:

Inserting Picture

Step 1: Open Your Excel Worksheet
Start by opening your Excel worksheet where you want to add illustrations.
Step 2: Insert a Picture
To insert a picture into your worksheet:
- Click on the cell where you want the picture to appear.

- Go to the "Insert" tab on the Excel ribbon.
- Click on the "Illustrations" button.
- Click on the "Picture" button.

- Choose any one option “Place in Cell” or Place over Cells.

- Browse your computer to select the image you want to insert.
- Click "Insert."

Excel will place the selected picture in the cell you clicked on or over the cells.
Note When inserting a picture in Excel, you're presented with two meaningful choices:

1. Place in Cell:
• Picture stays within the boundaries of a
single cell.

• Resizes with the cell if you adjust its size.

• Useful for precise alignment with cell data.
2. Place Over Cells:
• Picture extends beyond the borders of the cell.

• Can cover multiple cells and isn't confined to cell size.
• Great for creative layouts and designs.
Step 3: Resize and Move the Picture
After inserting the picture, you can resize it and move it around the worksheet:
- Click on the picture to select it. You'll see resizing handles around the edges.
- Click and drag any corner handle to resize the picture.
- To move the picture, click and drag it to the desired location.

Note You cannot move a picture inserted in a cell. Think of pictures in cells like statues in niches – they can't be moved around. If you want them bigger or smaller, adjust the cell size by changing its height and width.

Insert Shapes

To add shapes to your worksheet:

- Go to the "Insert" tab on the Excel ribbon.
- Click on the "Illustrations" button.

- Click on the "Shapes" button.
- Choose a shape from the dropdown menu (e.g., rectangle, circle, arrow).
- Click on the worksheet where you want to insert the shape.
- Click and drag to draw the shape.

You can also customize the shape's fill color, outline
color, and other properties by right-clicking the shape
and selecting "Format Shape."

Add SmartArt Graphics

SmartArt is a feature in Excel that allows you to create visual representations of information. To insert SmartArt:

- Go to the "Insert" tab on the Excel ribbon.
- Click on the "Illustrations" button.

- Click on the "SmartArt" button.
- Choose a SmartArt graphic that suits your purpose.
- Click "OK."

A SmartArt graphic will appear on your worksheet, and you can click on its individual elements to enter text and customize the graphic.

Important - Format and Arrange Illustrations

To format and arrange your illustrations:
- Click on an illustration to select it.
- Use the "Format" tab on the Excel ribbon to change the fill color, outline, and other formatting options.
- To arrange illustrations, right-click an item, and choose "Bring to Front" or "Send to Back" to control their layering.

Chapter 4.5 - Creating Charts

Creating charts in Microsoft Excel is a powerful way to visually represent your data. Here's a step-by-step guide on how to use charts in Excel:
Step 1: Prepare Your Data

Before you create a chart, make sure your data is well-organized. Typically, you'll have two columns: one for the category (X-axis) and one for the values (Y-axis).

Example:
Figure 98
Step 2: Select the Data
Highlight the data range you want to include in your chart, including both the category and value columns.
Step 3: Insert a Chart
- Go to the "Insert" tab in the Excel ribbon.

- Click on the type of chart you want to create (e.g., Column, Bar, Pie, Line). For instance, we have incorporated a column chart to visually represent the data.

Style Click here to change title Filters Elements
Figure 99
Step 4: Customize Your Chart
After inserting the chart, you can customize it further:
1. Chart Elements:
• Purpose: Customize your chart with titles, labels, and more.
• Cool Factor: Adds a personal touch, like giving your chart its own superhero outfit.

2. Chart Style:
• Purpose: Dress up your chart with colors and styles.

• Cool Factor: It's a makeover for your chart, making it both informative and stylish.

3. Chart Filters:
• Purpose: Control what data is shown in your chart.

• Cool Factor: Like having a remote control to spotlight specific data points and keep things clear.
Chart Elements
Chart Style Chart Filters
Data LabelsTrendline
Axis Title
Figure 100
Here is Chart elements options :

1. Chart Title: Click your chart, add a clear and catchy title using 'Chart Title' in Chart Elements Tools, and you're set to tell your data story at a glance!

2. Axis Titles: Click on the axis titles to add or edit them.
3. Data Labels: You can choose to display data labels on your chart by clicking on Data Labels.

4. Trendline: Adding a trendline to an Excel chart can help you visualize trends and make predictions. Click on the trendline in Chart Elements Options.

Step 5: Change Chart Type (Optional)
If you want to change the chart type after creating
it:

- Select the chart.

- Go to the " Design" tab in the Chart Tools.
- Click "Change Chart Type" and select the new chart type.

Figure 101
Step 6: Save and Share Your Chart
- To save your chart as an image, right-click on the chart and choose " Save as Picture."
- To copy and paste your chart into another document, right-click and select "Copy" and then paste it into the desired location.

That's it! You've created a chart in Microsoft Excel. Charts are a fantastic way to visualize data and make it easier for others to understand your information. You can experiment with different chart types and formatting options to best represent your data.

Chapter 4.6 Incorporating Hyperlinks

Adding hyperlinks in Microsoft Excel allows you to create interactive spreadsheet that link to web pages, files, email addresses, and more. Here's a step-by-step explanation with examples:

Step 1: Open Your Excel Worksheet
Start by opening the Excel worksheet where you want to add hyperlinks.
Step 2: Select the Cell for the Hyperlink
Choose the cell where you want to place the hyperlink. This is the cell that people will click on to access the linked content.
Step 3: Insert a Hyperlink
To insert a hyperlink:
- Right-click on the selected cell where you want to
create the link.
- In the context menu that appears, choose " Hyperlink
or Link."

Keyboard Shortcut : To insert a hyperlink, simply use the keyboard shortcut Ctrl + K.
In the "Insert Hyperlink" dialog box that appears, you can choose the type of hyperlink you want:

Figure 102
- Existing File or Web Page: If you want to link to a web page or a file on your computer, select this option.
- Place in This Document: If you want to create a link to another location within the same Excel document, choose this option.
- Email Address: To create an email link, select this option and enter the email address.

Link to a Web Page (Example)

Let's say you want to create a hyperlink to a web page:
1. Select "Existing File or Web Page."

2. In the "Address" field, enter the web address (URL) you want to link to (e.g., https://www.mylivesupport.in).
3. Optionally, you can enter a "Text to display" that will be visible to users (e.g., "My Live Support").
4. Click "OK" to create the hyperlink.
Figure 103
Test the Hyperlink
To test the hyperlink:- Click once on the linked text or cell. Excel will open your default web browser and take you to the linked web page.

Link to a File (Example)

If you want to link to a file on your computer:
1. Select "Existing File or Web Page."
2. Click on "Browse" to locate and select the file you want to link to (e.g., a PDF document).
3. Optionally, enter a "Text to display" (e.g., "Open PDF File"). 4. Click "OK" to create the hyperlink.
Figure 104

Test the Hyperlink (File Example)
To test the file hyperlink:

- Click on the linked text or cell. Excel will open the linked file using the associated software (e.g., Adobe Reader for a PDF).

Link to a Location in the Same Document (Example)

If you want to create a link to another location within the same Excel document:
1. Select "Place in This Document."
2. In the "Or select a place in this document" section, choose the sheet and cell where you want to link (e.g., Sheet2!A1).
3. Optionally, enter a " Text to display" (e.g., "Go to Sheet2"). 4. Click "OK" to create the hyperlink.

Test the Hyperlink (Document Example)
To test the document hyperlink:

- Click on the linked text or cell. Excel will take you to the specified location within the same document.

Link to an Email Address (Example)

To create a hyperlink that opens the user's default email program with a prefilled email:
1. Select "Email Address."
2. In the "Email address" field, enter the email address (e.g., "chandan@mylivesupport.in").
3. Optionally, enter a "Subject" for the email (e.g., "Regarding Our Meeting").
4. Click "OK" to create the email hyperlink.

Test the Hyperlink (Email Example)

To test the email hyperlink:

- Click on the linked text or cell. It will open your default email program with a new email window, pre-filled with the recipient's email address and subject.

That's it! You've successfully added hyperlinks in Microsoft Excel.

Remove Hyperlink

Method 1: Right-Click and Remove
1. Open your Excel document.

2. Locate the cell containing the hyperlink you
want to remove.

3. Right-click on the cell with the hyperlink.
4. In the context menu that appears, select "Remove Hyperlink." The hyperlink will be removed, and the cell's content will remain.

Method 2: Use the Remove Hyperlinks Feature
You can use the "Remove Hyperlinks" feature to remove multiple hyperlinks at once. Here's how:
1. Open your Excel document.
2. Select the range of cells containing hyperlinks that you want to remove.
3. Go to the "Home" tab on the Excel ribbon.
4. In the "Editing" group, click on the small
drop-down arrow under "Clear."
5. From the drop-down menu, select "Remove
Hyperlinks."
This will remove all hyperlinks from the selected
cells.

Chapter 4.7 Text to Columns function

The Text to Columns function in Microsoft Excel allows you to split text in a cell into multiple columns based on a delimiter, such as a comma or space. This is particularly useful when you have data in a single column that needs to be separated into different columns. Here's a step-by-step explanation with examples:

Example Data:

Assuming you have data in Column A and you want to split it into First Name (Column B) and Last Name (Column C). Let's break down the steps to use the Text to Columns function

Figure 105
Step 1: Open Your Excel Worksheet
Start by opening the Excel worksheet that contains the data you want to split into multiple columns.
Step 2: Select the Column or cells
- Click on the column header of the cell or select cells that contain the text you want to split.

Step 3: Open the Text to Columns Wizard
To open the Text to Columns Wizard:
- Go to the "Data" tab on the Excel ribbon.

- In the "Data Tools" group, click on the "Text to
Columns" button. This will open the Text to Columns Wizard.

Step 4: Choose a Data Type
In the Text to Columns Wizard, you'll see two options:

- Delimited: Use this option when your data is separated by a specific character, such as a comma, space, tab, or custom delimiter. Most text splitting tasks use this option.

- Fixed Width: Use this option when your data has a consistent width for each column, and you want to split it based on character positions.

Step 5: Choose Delimited:
• Since your data is separated by a space, choose " Delimited." and click on Next.
Step 6: Select Space as the Delimiter:

• In the next window, select "Space" as the delimiter (This tells Excel to split the data wherever there's a space) and click on Next.

- Excel will show a preview of how your data will be split based on the selected delimiter. Ensure the preview matches your data.
Step 6: Specify Destination for Split Data:
• Decide where to place the split data; for instance, select "Column B" or click on "B1" cell based on your preference.
Input the preferred cell reference in this field, and feel free to delete the existing one if needed.
Step 7: Finish the Wizard
- Click the "Finish" button when you're satisfied with your settings.
Step 8: Review the Result : Column A will now be split into two columns: Column B with first names and Column C with last names.

By following these steps, you can easily split text into separate columns in Excel, making it more organized and structured for your data analysis or reporting needs.

Chapter 4.8 Removing Duplicates

The "Remove Duplicates" function in Microsoft Excel allows you to quickly eliminate duplicate values from a range or column of data, helping you clean and streamline your dataset. Here's a step-by-step explanation with example:

Step 1: Open Your Excel Worksheet
Start by opening the Excel worksheet containing the data from which you want to remove duplicates.
Step 2: Select the Data Range
- Click on any cell within the column or range where you want to remove duplicates. This will highlight the entire column or range.

Step 3: Open the Remove Duplicates Dialog
To open the Remove Duplicates dialog:
- Go to the "Data" tab on the Excel ribbon.

- In the "Data Tools" group, click on the "Remove Duplicates" button. This will open the Remove Duplicates dialog box.
Step 4: Choose Columns

In the Remove Duplicates dialog box, you'll see a list of all the columns in your selected data range. By default, all columns will be selected. You can choose which columns you want to check for duplicates. For efficient removal of duplicate entries in large datasets, consider selecting all columns.

Step 5: Remove Duplicates
- After selecting the columns, click the "OK" button. Excel will analyze the selected columns for duplicate values.
Figure 108
Step 6: Review Results

After removing duplicates, Excel will display a message indicating how many duplicate values were removed and how many unique values remain. Click "OK" to close the message.

By following these steps, you can easily remove duplicate values from your Excel dataset, making it cleaner and more suitable for analysis, reporting, or other purposes.

Chapter 4.9 Using Flash Fill

Flash Fill is a powerful feature in Microsoft Excel that helps you quickly extract, combine, or format data without using complex formulas. It automatically detects patterns in your data and applies the desired transformation. Here's a step-by-step explanation with examples:

Step 1: Open Your Excel Worksheet
Start by opening the Excel worksheet where you have the data you want to manipulate using Flash Fill.
Step 2: Identify the Transformation Pattern

Before using Flash Fill, identify the transformation pattern you want to apply to your data. This could involve splitting, combining, or reformatting text or numbers. You'll need to show Excel the pattern using examples.

Step 3: Set Up Your Data Table

Create a table in your worksheet to illustrate the transformation pattern. In this table, include both the original data and the desired result. Excel will use this as an example to understand the pattern.
Here's an example table for splitting a full name into first and last names:

Figure 109
Step 4: Start Flash Fill
- In the "First Name" column (Cell B2), start typing the first name of the first example (e.g., "John").

- Press "Enter" to confirm the entry.
- Now, go to the " Data" tab on the Excel ribbon.

- In the "Data Tools" group, click on the "Flash Fill" button (it looks like a lightning bolt).
Figure 110
Excel will automatically detect the pattern based on your example and fill in the remaining first names.
Figure 111
Step 5: Apply Flash Fill for Last Names
Repeat the same process in the "Last Name" column (Cell C2). Start typing Enter," and then use Flash Fill

) to automatically fill in the remaining last names.
the last name (e.g., "Smith"), press " (Ctrl+E

Before Applying Flash Fill in Column C After Applying Flash Fill in Column C Figure 112
Step 6: Review the Result

Excel will apply the transformation pattern to the entire column based on your examples. Review the data to ensure that Flash Fill correctly extracted or formatted the information.

Extracting PAN Numbers from GST Numbers using Flash Fill:

1. Dataset: Imagine you have a column (Column A) filled with GST numbers, and you want to extract PAN numbers from them into Column B.

Figure 113
2. Steps:
• In Cell B2, type "PAN Number (AAACF3272A)" to let Excel know the pattern you want.
Before Applying Flash Fill in Column B
• Press Enter.
After Applying Flash Fill in Column B
• Now, press Ctrl + E (Flash Fill Shortcut).

3. Result: Marvel as Excel magically extracts all PAN numbers from the GST numbers in Column A and fills them into Column B! By following these steps, you can use Flash Fill to quickly manipulate and format data in Excel based on patterns and examples, saving you time and effort.

Having Trouble with Flash Fill?

If Flash Fill doesn't automatically work after pressing Ctrl + E, no worries! Simply select all the cell where you want to apply the Flash Fill, and then press Ctrl + E. Let Excel know what you need, and it'll take care of the rest.

Explore More Flash Fill Magic:

Take a look at these screenshots (see figure 114) showcasing additional examples achieved with Flash Fill. You can apply the same process we used in the earlier practical example to achieve similar results.

Simply follow the steps we discussed earlier, and let Flash Fill effortlessly handle your data transformations. It's like having Excel read your mind!
Figure 114
Note Flash Fill is a cool feature available in Excel 2013, Excel 2016, Excel 2019, Excel 2021, and, of course, Excel for Microsoft 365.

Chapter 4.10 Transposing Data

Transposing data in Excel means switching the rows and columns, effectively rotating your data. There are several methods to transpose data, and I'll explain some of them step by step with tables and examples:

1. The Traditional Copy-Paste Method:

• Select your Data: Highlight the cells you want to transpose.
• Copy: Press Ctrl + C on your keyboard.
Figure 115
• Choose Destination: Move to the location (e.g., E1) where you want to paste. Right-click and choose " Paste Special."
• Transpose: Check the "Transpose" option and click "OK."
Data Transposed
Figure 116
The transposed data will appear in cells E1 to H3. This traditional method is useful for small data.

2. The Transpose Function:

• Select Destination: Pick the cell (e.g., E1) where you want the transposed data
• Type the Formula: Begin in the chosen cell by entering =TRANSPOSE(
• Select Original Data: Highlight the cells with your original data.
Figure 117
• Close the Formula: Type ) and press Ctrl + Shift + Enter
Example: If you're transposing data from A1 to C4, you would start in E1 and enter =TRANSPOSE(A1:C4) and Press Ctrl + Shift + Enter
Additional Tip for Excel 365 Users:

In Excel 365, if pressing " Ctrl + Shift + Enter" for the transpose formula doesn't work, simply hit " Enter" – it should do the trick. Keep it straightforward!

Note For small & larger datasets, you can use the TRANSPOSE function.

These methods allow you to quickly and efficiently transpose data in Excel, whether you're working with small or large datasets. Choose the method that suits your specific data and needs.

Chapter 4.11 Copy and Paste Visible Data

Copying Visible Cells in Excel

If you have a table with both visible and hidden cells, you can easily copy only the visible data using the "Alt + ;" keyboard shortcut. Here's a simple guide:

Step 1: Select Your Data
• Highlight the range of cells that includes both visible and hidden data.
Figure 118
Step 2: Press Alt + ;
• Simultaneously press Alt + ; on your keyboard to select only the visible cells within your chosen range.
Step 3: Copy and Paste
• Right-click on the selected visible data, choose "Copy," move to your desired location, and paste. You can paste as values if needed. That's it! 🚀This shortcut is handy for working with filtered or hidden rows in Excel.

Chapter 4.12 Running Total with Quick Analysis

To calculate a running total in Excel using the Quick Analysis tool, follow these step-by-step instructions:
1. Prepare Your Data: Ensure your data is organized, and cells to the right of your data are empty.
2. Select Your Data: Click and drag to select the range of cells, including the initial value and subsequent values to sum.
Figure 119 Quick Analysis Tool
3. Quick Analysis Tool: Look for the "Quick Analysis Tool" at the bottom-right corner, click on it.
4. Access Analysis Options: In the menu, click on "Total." 5. Choose Running Total: Under "Total" options, select "Running Total." which is usually highlighted in yellow

6. Observe the Running Total: Excel will auto-calculate the running total in the next column. Each cell shows the cumulative sum up to that point.

Select Data
Figure 120

Now, you have a running total of your data without the need to create complex formulas. This feature is especially handy for quick data analysis and reporting in Excel.

Chapter 4.13 Restore Sorted Data

If you've sorted your Excel data and now want to restore it, follow these steps:

1. Ensure Data Has Serial Numbers: Before sorting, make sure your data has a serial number or index in Column A. This is crucial for restoring the original order.

2. Sort Data Item Wise: Initially, your data might be unsorted. Sort it by item name or any other criteria you need.
Figure 121

Restore Data Using Serial Numbers:

To restore the original order, sort the data based on the serial number (Column A). Here's how:
Apply Filter:
• Press Ctrl + Shift + L to apply a filter to your data. This step makes it easier to sort and restore.
Sort Data Based on Serial Numbers:

• Click on the drop-down arrow in the header of Column A (where your serial numbers are). (see figure 122)
• Choose "Sort Ascending" or "Sort Smallest to Largest."

Figure 122
By following these steps, you'll effectively sort your data based on the serial numbers in ascending order, restoring the original sequence.
Figure 123
Now, your data is back to its original sequence. Always keep those serial numbers handy for such quick restoration!

Chapter 4.14 Filter Data with Blank Rows

You can filter data in Excel even if there are blank rows between entries. Here's how to do it:

1. Select Your Data: Click on the first cell that contains your data (e.g., "S.No." in Column A), and then hold down the Shift key while clicking on the last cell containing data. This will select all your data, including the blank rows.

Figure 124

2. Apply the Filter: Once your data is selected, go to the "Data" tab in the Excel ribbon and click on the "Filter" button. This will apply the filter to your selected data.

Keyboard Shortcut - Press Ctrl + Shift + L to apply a filter to your data
Filter Your Data: Now, you'll see filter arrows in your column headers. Click on the arrow in the column you want to filter (e.g., "Item Name" or "Rate"). You can then select the specific items you want to filter or use various filter options.

Figure 125

That's it! Your data will be filtered based on your criteria, even if there are blank rows in between. This makes it easy to work with irregularly spaced data in Excel.

Chapter 4.15 Calculating Age

Calculating age in Excel is straightforward. You need to have a birthdate or date of birth and the current date to find someone's age. Here's how you can do it with an example:

Let's assume you have the following data:
• Cell A2: Date of Birth (e.g., 08-06-2001)
• Cell B2: Current Date (e.g., 17-11-2023)

Figure 126
You want to calculate the age in years. You can use the DATEDIF function for this:
1. In Cell C2, where you want to display the age, enter the following formula(see figure 126):
=DATEDIF(A1, B1, "y")

Here's what the formula means:
• A1 is the birthdate.
• B1 is the current date.

• "y" specifies that you want to calculate the difference in years.

2. Press Enter.
Now, Cell C2 will display the person's age. Here's a table with the example values:

Figure 127
If you want to calculate age in a month or Days, you can mention in the formula :
“M” for Month - calculates the difference in months.
“D” for Days - calculates the difference in days.
This is a simple way to calculate age in Excel, assuming you have the birthdate and current date.
Bonus Tip: To get the current date, simply use the formula =TODAY().

Chapter 4.16 Basic Dropdown List

Creating a dropdown list in Excel is a useful way to control data input and make your spreadsheets more user-friendly. Here's a step-by-step guide:

Basic Dropdown List In Excel Step 1: Prepare Your List

Start by creating a list of items you want to include in your dropdown. For example, you may have a list of fruits like this:
Figure 128

Step 2: Select the Cell or Cells for the Dropdown

Choose the cell or cells where you want the dropdown list to appear. Let's say you want it in cell B2.

Step 3: Apply Data Validation

Go to the "Data" tab in the Excel ribbon and click
on "Data Validation" in the "Data Tools" group.

Step 4: Set Up Data Validation

In the "Data Validation" dialog box:
- In the "Allow" dropdown, select "List."

- In the "Source" field, select the range of cells that contains your list. In this example, that's A2:A5. You can manually enter this range or click the small icon to select it.

Figure 129
Step 5: Test the Dropdown

Click OK. Now, when you click on cell B2,
you will see a dropdown arrow. Clicking on
it will display the list of fruits you created.

You can select any fruit from the dropdown list, and it will appear in cell B2. This ensures that only valid choices are entered into your spreadsheet, making data entry more efficient and accurate.
That's how you create a basic dropdown list in Excel. You can customize and enhance dropdown lists further by using features like dynamic lists, dependent dropdowns, and more as needed for your specific spreadsheet needs.

Simple Dropdown List

You can create a simple dropdown list for 2 to 3-word text options (e.g., apple, orange, banana) directly within Data Validation in Excel. Here's a step-by-step guide :

Step 1: Select the Cell or Cells for the Dropdown List
Choose the cell where you want the dropdown list to appear. Let's say you want it in cells B2:B6.
Step 2: Apply Data Validation
Go to the "Data" tab in the Excel ribbon and click on
"Data Validation" in the "Data Tools" group.
Step 3: Set Up Data Validation
In the "Data Validation" dialog box:

- In the "Allow" dropdown, select "List."
- In the "Source" field, you can directly type your text options separated by commas. For example, type apple, orange, banana .

Figure 130
Step 4: Test the Dropdown

Click OK. Now, when you click on cell B2:B6, you
will see a dropdown arrow. Clicking on it will display
the list of text options you typed.

You can select any of the text options from the
dropdown list, and it will appear in selected cell (e.g.
B2, B3, B4).

Note You can copy the cell with the dropdown list and paste it anywhere in your Excel sheet to replicate the dropdown list. This allows you to use the same list in multiple locations within your spreadsheet for consistent data entry.

That's how you create a simple dropdown list for 2 to 3-word text options directly within Data Validation in Excel. This method is quick and convenient when you have a small set of choices and don't need to maintain a separate list in your spreadsheet.

Chapter 4.17 Dynamic and Unique DropDown List

Creating a dynamic, unique dropdown list without duplicates in Excel involves using a Named Range and Data Validation. Here's a step-by-step guide:

Step 1: Convert Data to an Official Table

Start by converting your data into an official Excel Table. Select the range containing your data, including the header (if you have one), and go to the "Insert" tab. Click on "Table" or Press Ctrl + T to convert the data into a table.

Figure 131
After converting it to a table, Excel will automatically name it "Table1."
Click on Table Design Tab to see Table Name.

Step 2: Create a Named Range for Unique Values

Select Item Name (excluding header) that you want to use for your dropdown. Go to the " Formulas" tab in the Excel ribbon and click on "Name Manager." Click "New" to create a new name for your range.

Mention Name
without Space
Excel will deduct this Range Automatically Select Data

Step 3: Select the Cell or Cells or Column for the Dropdown

Choose the cells or Column where you want the unique dropdown list to appear. Let's say you want it in Column C.

Step 4: Apply Data Validation

Go to the "Data" tab in the Excel ribbon and click on "Data Validation" in the "Data Tools" group.

Step 5: Set Up Data Validation

In the "Data Validation" dialog box:
- In the "Allow" dropdown, select "List."

- In the "Source" field, enter =ItemName. This references the named range you created(see figure 133), which contains unique values. (Or Press F3 to select the named range you created)
Step 6: Test the Unique Dropdown

Click OK. Now, when you click on any cell in Column C, you will see a dropdown arrow. Clicking on it will display the unique list of items from your data, without duplicates.

Figure 134

You can select any item from the unique dropdown list, and it will appear in Column C cells . This ensures that you only have unique values in your dropdown, making data entry more efficient.

That's how you create a dynamic and unique dropdown list without duplicates in Excel, using a named range and data validation. This method ensures that your dropdown list updates automatically as you add or remove items from your table.

Chapter 4.18 Save Excel Sheet In PDF

To save or export an Excel sheet in PDF format, follow these step-by-step instructions:
Step 1: Open Your Excel Workbook
Open the Excel workbook containing the sheet you want to save as a PDF.
Step 2: Navigate to the Sheet You Want to Export
Click on the sheet tab at the bottom to make sure you're on the sheet you want to save as a PDF.
Figure 135
Step 3: Press F12 button on the Keyboard
Press F12 (or Fn + F12 on a laptop) to open the Save As window.
Step 4: Choose the Location to Save the PDF
• Navigate to the location on your computer where you want to save the PDF file.
Choose Location
Name the PDF File
Choose PDF From DropDown
Figure 136
Step 5: Name the PDF File
• In the "File Name" field, give your PDF file a name.
Step 6: Select "PDF (*.pdf)" as the File Type
• Below the "File Name" field, you'll find a drop-down menu for choosing the file type. Select "PDF (*.pdf)" from the list.

Step 7: Click "Save"
• After choosing the PDF format, click the "Save" button.

Your Excel sheet will be saved as a PDF in the location you specified. You can now find and access the PDF file in the folder you selected.

Chapter 4.19 Save Excel Selected Area in PDF

To save a selected area from an Excel sheet as a PDF, you can follow these steps:
Step 1: Open Your Excel Workbook
Start by opening your Excel workbook, which contains the data you want to save as a PDF.
Step 2: Select the Area

Click and drag your mouse cursor to select the specific area in the Excel sheet that you wish to save as a PDF. Make sure the chosen area is highlighted.

Figure 137
Step 3: Press F12 button on the Keyboard
Press F12 (or Fn + F12 on a laptop) to open the Save As window.
Step 4: Choose the Location to Save the PDF
• Navigate to the location on your computer where you want to save the PDF file.
Step 5: Name the PDF File
• In the "File Name" field, give your PDF file a name.
Choose Location
Name the PDF File

Choose PDF From DropDown Click here for more
options to save pdf

Figure 138
Step 6: Select "PDF (*.pdf)" as the File Type
• Below the "File Name" field, you'll find a drop-down menu for choosing the file type. Select "PDF (*.pdf)" from the list.
Step 7: Access Options
Look for an "Options" or "Publish Options" button and click on it.(see figure 139)
Figure 139
Step 8: Specify Selection
Figure 140

In the options box, find the " Publish What" or similar setting. Choose the "Selection" option to indicate that you want to save the currently selected area.(see figure 140)

Step 9: Save the PDF
After configuring your options, click the "OK" or "Save" button.

Your selected area from the Excel sheet will now be saved as a PDF in the destination folder you specified. You can locate and access the PDF file there.

Chapter 4.20 Link Any Excel Data With MS Word

Here's a step-by-step explanation of how to link Excel data to MS Word with automatic update:
Step 1: Open Your Excel Workbook
• Begin by opening your Excel workbook that contains the data you wish to link to your Word document.
Step 2: Select and Copy the Data

• Next, select the specific data or table you want to link in Excel. You can do this by clicking and dragging your mouse to highlight the data. Once it's selected, press Ctrl + C on your keyboard or rightclick the selected data and choose "Copy."

Figure 141
Step 3: Open MS Word
• Now, open your Microsoft Word document where you want to insert the Excel data.
Step 4: Paste the Data

• Position your cursor within your Word document where you'd like the Excel data to appear. Then, paste the data by pressing Ctrl + V or by right-clicking and choosing "Paste."

Figure 142
Step 5: Use the Paste Option Icon
• After pasting the data into Word, you'll notice a paste option icon in the bottom right corner of your data. Click on this icon.
Step 6: Choose Link & Keep Source Formatting

• In the paste options, choose "Link & Keep Source Formatting" or simply press the "F" key on your keyboard. This option ensures that the data is linked and maintains its original formatting.

By following these steps, your Excel data will stay linked and updated in your MS Word document, making it easier to maintain and display up-todate information.

Chapter 4.21 Page Setup and Print Data

The Page Setup group in Microsoft Excel offers various options to customize the way your worksheet will appear when printed. Here's a stepby-step guide with explaining each of these options:

Margins

Explanation: Margins determine the spacing between the content and the edges of the printed page. These are crucial for a neat and organized printout.

How to Set:

• Click on the "Page Layout" tab.
• Choose "Margins" and select options like Normal (default), Wide, or Narrow.

Example: In professional reports, wider margins are often preferred for a polished appearance. However, narrow margins prove beneficial when working within strict page limits. They allow for more content on each page, making them practical for information-dense reports with space constraints.

Orientation

Explanation: This sets the direction of the paper - portrait (vertical) or landscape (horizontal). Useful for adapting to the content of your sheet. How to Set:
• In the "Page Layout" tab, click on "Orientation" and select Portrait or Landscape.
Example: Landscape might be ideal for a wide table that doesn't fit well in portrait mode.

Page Size

Explanation: Page size defines the dimensions of
your paper. It can be standard (Letter, Legal) or
custom.

How to Set:
• In the "Page Layout" tab, click on "Size" and
choose a standard size or define a custom size.

Example: Selecting A3 size might be suitable for
printing a large diagram. However, the majority of
offices prefer using A4 size paper when printing Excel
data.

Print Area

Explanation: Specify the range of cells you want to include in your printout. Helpful to avoid unnecessary printing.
How to Set:

• Select the cells you want to print.
• Click on "Print Area" and choose "Set Print
Area."

Example: If your sheet has a table of data, set the
print area to focus on that specific range.

Note - If you want to print a different set of Excel data on the same sheet, you'll need to clear the existing print area. To do this, click on 'Print Area' and then select 'Clear Print Area.

Breaks

Explanation: Page breaks control where Excel starts a new page. Useful for maintaining readability.
How to Set:

• Select a cell below or to the right of where you
want the page break.
• In the "Page Layout" tab, click on "Breaks" and
choose "Insert Page Break."

Example: If you have a long list of items, insert page breaks to start each new category on a new page.

Background

Explanation: Add a background color or image to your sheet for visual appeal. Useful for presentations or reports.
How to Set:
• In the "Page Layout" tab, click on "Background"
and choose a color or insert an image.
Example: A light blue background might enhance the readability of your financial report.

Print Titles

Explanation: Ensure specific rows or columns are repeated on each printed page. Useful for headers or labels.
How to Set:

• In the "Page Layout" tab, click on "Print Titles."
• Choose the rows or columns you want to repeat on each page.
Example: If your sheet has a header row, set it as a print title to appear at the top of every page.

These Page Setup options give you control over how your Excel sheet looks when printed. They help you tailor the output to your specific needs, whether it's for reports, presentations, or other documents. Just remember to click "OK" to apply your settings after making adjustments.

How to Use Print Titles

Step 1: Open Your Excel Workbook
• Open the Excel workbook that contains the sheet you want to print.
Step 2: Navigate to the Page Layout Tab

• Click on the "Page Layout" tab at the top of the Excel window. Step 3: Select Print Titles Option
• In the "Page Layout" tab, find the "Print Titles" option. It's usually in the Page Setup group.

Step 4: Define Rows or Columns to Repeat
• Click on "Print Titles." A Page Setup dialog box will appear.

• In the "Sheet" tab of the dialog box, you will see two boxes labeled "Rows to repeat at top" and "Columns to repeat at left."
Step 5: Select the Rows or Columns to Repeat
• Navigate to your worksheet and select the rows or columns you want to repeat on each printed page.
• After selecting, go back to the Page Setup dialog box. For Older Excel Versions: Selecting Rows or Columns to Repeat
• In older Excel versions, click on the small icon at the right end of each box to choose the rows or columns you want to repeat.
• Once selected, click on the small icon again to expand the window and confirm your selection.
Figure 143
Step 6: Click OK to Apply
• Once you've selected the desired rows or columns, click "OK" in the Page Setup dialog box to apply the changes.
Step 7: Check Print Preview
• Go to the "File" tab, click on "Print," or use the shortcut Ctrl + P to enter the Print Preview.
• Ensure that your selected rows or columns appear on each page.
Step 8: Adjust as Needed
• If needed, return to the "Print Titles" option in the "Page Layout" tab to make adjustments.
Step 9: Print Your Worksheet
• Once you're satisfied with the Print Titles setup, proceed to print your worksheet using the Print button.

By following these steps, you can easily set up and use Print Titles in Excel to ensure specific rows or columns are repeated on each printed page, providing clarity and context to your printed documents.

Additional Printing Tips

Printing Made Perfect: Adjusting Margins and Scaling

When you're about to print your Excel masterpiece and notice some data slipping onto another page, don't worry. Excel has your back with margin and scaling settings. Here's how to make everything fit like a charm:

Click on the Page Setup Icon:

• Look for the "Page Setup" icon. It's like the
control center for making your printouts look
fabulous.

Page Setup Dialog Box Appears:

• Once you click, a dialog box pops up. This is where the magic happens. (see figure 144)
Tune Scaling on the Pages Tab:

• Head over to the "Pages" tab. Here, you'll find the scaling options. Think of it as your zoom control for printing.
Figure 144
Adjust Scaling Percentage:

• Click on "Adjust to" and change the percentage until your data fits right. A little tip: stay above 85% to keep things visible and readable.

Handle Margins Like a Pro:
• Now, switch to the "Margins" tab. This is where you control the spaces around your content.
Figure 145
Fine-Tune Top, Bottom, Left, Right, Header, and Footer:

• Each section is like a magic slider. Adjust the Top, Bottom, Left, and Right margins until your content is perfectly framed. Don't forget the Header and Footer—they deserve some attention too!

Hit "OK" to Seal the Deal:
• Once you're done, click "OK." Your Excel sheet is now ready to print perfectly.

Part V: Formulas and Functions

Chapter 5.1 - Understanding Formulas And Function

In Microsoft Excel, formulas and functions are the building blocks that allow you to perform calculations and manipulate data. Here's a explanation of what they are and how they differ:

Formulas : Think of formulas as your personal math wizard in Excel. A formula is a combination of numbers, operators (like +, -, *, /), and cell references (like A1, B2) that perform calculations. These calculations can be simple additions or complex equations. For example, you can use a formula like "=A1+B1" to add the contents of cells A1 and B1.

Formulas are created by typing an equal sign (=) into a cell and then entering the mathematical expression. Excel uses this formula to calculate the result automatically. If you change the data in the cells referenced by the formula, Excel updates the result accordingly. Formulas are excellent for custom calculations and are like having your own calculator built into your spreadsheet.

Functions : Functions, on the other hand, are like ready-made Excel recipes. They are predefined formulas that are designed for specific tasks. Functions take arguments, which are the input values, and then they perform operations on those inputs. You don't need to know the underlying mathematical operations; you just need to use the correct function and provide the necessary data.

For example, the SUM function takes a range of cells as its argument and calculates the sum of those cells. To use it, you'd write "=SUM(A1:A5)" to find the total of cells A1 through A5.

The Difference : The key difference between formulas and functions is that formulas are user-created, tailored to your specific needs, while functions are pre-built and optimized for common tasks.

Chapter 5.2 - Creating formulas and functions

Creating formulas and functions in Microsoft Excel is like using a magic wand to perform calculations automatically. It allows you to perform mathematical operations, manipulate data, and make your spreadsheet work for you. Here's a detailed explanation with examples:

Creating Formulas and Functions in Excel:

1. Select a Cell: Start by selecting the cell where you want the result of your calculation to appear. This is usually where you want to see the answer to your formula.

2. Begin a Formula: To create a formula, you need to start with an equals sign (=). This tells Excel that you're entering a calculation.

3. Select Cells or Enter Values: After the equal sign, select the cells containing the data you want to use in your calculation, or enter the values directly.

4. Choose an Operator: After the selecting cell, you can use various operators:

- Addition (+): To add numbers together.
- Subtraction (-): To subtract one number from another.
- Multiplication (*): To multiply numbers.
- Division (/): To divide numbers.
- Exponentiation (^): To raise a number to a power (e.g., 2^3 = 8).

5. Again Select the Cell or Enter Value : After the operator, select the second cell containing the data you want to use in your calculation, or enter the values directly.

6. Close the Formula: To finish your formula, press Enter. Excel will calculate the result and display it in the selected cell.

Examples:

- Simple Addition:In cell C1, type =A1+B1 and press Enter. If A1 contains 5 and B1 contains 3, cell C1 will display 8 (5 + 3).

- Subtraction: In cell G1, type =E1-F1 and press Enter. If E1 contains 10 and F1 contains 4, cell G1 will display 6 (10 - 4).

- Multiplication: In cell J1, type =K1*3 and press Enter. If K1 contains 7, cell J1 will display 21 (7 * 3).

- Division: In cell C2, type =B2/2 and press Enter. If B2 contains 8, cell C2 will display 4 (8 / 2).

- Exponentiation: In cell M1, type =N1^2 and press Enter. If N1 contains 5, cell M1 will display 25 (5^2).

Using Functions:

Functions are pre-built formulas in Excel that perform specific tasks or calculations. To use a function:
1. Select a Cell: Just click on the cell where you want the answer to appear.
2. Start with Equals Sign: Begin by typing an equals sign (=).

3. Function Name: After the equals sign, type the name of the function. Think of it as telling Excel which magic trick you want it to perform.

4. Open Parentheses: Now, add an opening parenthesis ( . It's like saying, 'Hey Excel, get ready for some instructions!'

5. Add Arguments: Inside the parentheses, put in the info that the magic trick needs. This could be cell references, numbers, or whatever the function requires. The arguments for the function separated by commas ( , ).

6. Close the Function: Finish up by closing the parentheses ) and hit Enter. It's like saying, Excel, do your thing!'"
Example using SUM function:

- In cell B1, type =SUM(A1:A5) and press Enter. If cells A1 to A5 contain the numbers 10, 20, 30, 40, and 50, cell B1 will display the sum of these values, which is 150.

Figure 146

Excel functions are like shortcuts for common calculations, saving you time and ensuring accuracy in your spreadsheets. Whether it's simple arithmetic or complex statistical analysis, Excel's formulas and functions can handle a wide range of tasks.

Chapter 5.3 SUM Function

Summing Excel data is a common task, and there are multiple ways to achieve it using formulas and functions. Here are three ways to SUM data in Excel :

1. SUM Function:

- What It Does: The SUM function adds up a range of numbers.
- Formula Syntax: `=SUM(range)`
- Example: Suppose you have a list of numbers in cells A1 to A5, and you want to find the sum of these numbers.

Steps:
1. Select the cell where you want the sum to appear (e.g., cell B1).
2. Type =SUM(A1:A5) and press Enter.

Figure 147

3. Cell B1 will display the sum of the numbers in A1 to A5. If cells A1 to A5 contain the numbers 10, 20, 30, 40, and 50, cell B1 will display the sum of these values, which is 150.

2. AutoSum Button:

- What It Does: Excel provides a quick way to SUM data using the AutoSum button on the toolbar.
- Steps:
1. Select the cell where you want the sum to appear. (e.g. B6, C6, D6)
Figure 148
2. Click on the 'AutoSum' button (Σ) in the Home tab to initiate the calculation. This will automatically insert the SUM formula.

After clicking the AutoSum button, the sum of the selected cells will be displayed(see figure 149), and the SUM function will be automatically applied to include all relevant cells. You can customize this function as needed.

Example of AutoSum:Imagine you have a list of items you've sold in a

store, along with the quantities sold for each year (2021, 2022, and 2023). You want to find out the total sales for all items for the entire year, and also the total sales for each item across the three years.

To find the total sales for all items in the whole year:
• Go to the row where you want to calculate the total (let's say Row 6).

• Choose the empty cells in the row corresponding to the desired year (specifically B6, C6, D6) to display the total sales quantities for that specific year.

Figure 150
• Click on the AutoSum button.

After clicking the Autosum button, the system will automatically calculate and display the total amount for all items corresponding to the specified year.

Figure 151

To find the total sales for each item across the three years:

• Go to the column where you want to calculate the total sales (let's say Column E).

• Choose the empty cells in column E (specifically E2, E3, E4, E5 and E6 for Grand Total) where you wish to display the total sales quantities for each respective item.

Figure 152
• Click on the 'AutoSum' button (Σ) in the Home tab to initiate the calculation.

After clicking the Autosum button, the system will automatically calculate and display the total amount for all items across the specified three-year period. (E6 display the Grand Total of All Items)

3. Manual Formula:

- What It Does: You can manually create a SUM formula for more control over the range.
- Formula Syntax: `=number1 + number2 + ...`
- Example: Let's say you have a list of numbers in cells C1 to C4 and you want to sum them manually.

Steps:
1. Select the cell where you want the sum (e.g., cell D1).
2. Type =C1 + C2 + C3 + C4 and press Enter.
3. Cell D1 will display the sum of the numbers in C1 to C4.

These methods make it easy to sum data in Excel, whether you prefer using functions, the AutoSum button, or manually entering formulas. Excel provides flexibility to suit your preferred approach.

Chapter 5.4 Subtracting Data

Subtracting data in Excel is a common task, and there are a few different ways you can do it. Let's learn how to subtract data in Excel in a simpler way:

1. Subtract Using a Formula:

- What It Does: You can subtract numbers in Excel using a simple formula.
- Formula Syntax: =number1 - number2

- Example: Suppose you have two numbers in cells A1 and B1, and you want to subtract B1 from A1.

Steps:
1. Select the cell where you want the result to appear (e.g., cell C1).
2. Type =A1 – B1 and press Enter.

3. Cell C1 will display the result of subtracting the value in B1 from the value in A1.

The result of the calculation (100 - 30) will appear in cell C1. The result in C1 will be 70 (100 - 30).
Example Suppose you have an Excel spreadsheet, and you want to perform the following calculations:

1. Sum the values in A1 and B1.
• Cell A1 contains the value 100.
• Cell B1 contains the value 50.

2. Subtract the value in C1 from the sum of A1 and B1.
• Cell C1 contains the value 30.
3. Get the result in D1.

Now, let's break down the steps:
1. Select cell D1:
• Click on cell D1 to indicate that this is where you want the final result.
2. Type the formula:
• In cell D1, type the following formula:
=A1 + B1 - C1
3. Press Enter.
• This formula instructs Excel to add the values in cells A1 and B1 and then subtract the value in cell C1.
4. View the result:
• The result of the calculation (100 + 50 - 30) will appear in cell D1 (120).

Chapter 5.5 Multiplying Data

Multiplying data in Excel can be achieved using various methods, including formulas and functions. Here are two ways to multiply Excel data, each explained step by step with examples:

1. Using a Formula:

- What It Does: You can multiply numbers in Excel using a simple formula.
- Formula Syntax: =number1 * number2
- Example: Let's say you have two numbers in cells A1 and A2, and you want to multiply them.

Steps:
1. Select the cell where you want the result to appear (e.g., B1).
2. Type =A1 * A2 and press Enter.

3. Cell B1 will display the result of multiplying the value in A1 by the value in A2.
So, if A1 contains 15 and A2 contains 20, the result in B1 will be 300 (15*20).

2. Using the PRODUCT Function:

- What It Does: The PRODUCT function calculates the product of a range of numbers.
- Formula Syntax: =PRODUCT(range)
- Example: Suppose you have a list of numbers in cells A1 to A5, and you want to find the product of these numbers.

Steps:
1. Select the cell where you want the product to appear (e.g., B1).
2. Type =PRODUCT(A1:A5) and press Enter.
3. Cell B1 will display the product of the numbers in A1 to A5.

Figure 154
Explanation:
• =PRODUCT(A1:A5):
• This tells Excel to multiply all the values in the range A1 to A5.
• Example Values:

• If A1 = 2, A2 = 3, A3 = 4, A4 = 5, and A5 = 6, then the result will be 2 * 3 * 4 * 5 * 6 = 720.
So, the PRODUCT function helps you quickly find the total multiplication of a range of numbers.

Bonus Tip: Handling Zeros in Multiplication:

Keep in mind that when you multiply by zero (0) in Excel, the result will always be zero. This is important to remember when working with multiplication.

These methods provide you with flexibility when multiplying data in Excel, whether you prefer using simple formulas, Excel functions like PRODUCT, or manual calculations. Excel allows you to easily perform multiplication tasks for various purposes.

Chapter 5.6 Division Data

Dividing data in Excel can be done using various methods, including formulas and functions. Here are two ways to divide Excel data, each explained step by step with examples:

1. Using a Formula:

- What It Does: You can divide numbers in Excel using a simple formula.
- Formula Syntax: =number1 / number2

- Example: Let's say you have two numbers in cells A1 and A2, and you want to divide A1 by A2.

Steps:
1. Select the cell where you want the result to appear (e.g., B1).
2. Type =A1 / A2 and press Enter.

3. Cell B1 will display the result of dividing the value in A1 by the value in A2.
So, if A1 contains 100 and A2 contains 5, the result in B1 will be 20 (100/5).
2. Manually Calculating the Division:
- What It Does: You can manually calculate the division by dividing numbers individually.
- Formula Syntax: =number1 / number2

- Example: Let's say you have number1 500and number2 5, and you want

to calculate the division manually.

Steps:
1. Select the cell where you want the result (e.g., F1).
2. Type =Number1 / Number2 and press Enter.

3. Cell F1 will display the result of dividing the value (500/5)

So, if Number1 is 500 and Number2 is 5, the result in cell
F1 will be 100 because 500/5=100.
Bonus Tip: Avoid Dividing by Zero:

Be cautious when dividing in Excel. Dividing by zero (0) results in an error (#DIV/0!). To avoid this, you can use an IF statement or check for zero values before performing division.

These methods provide you with flexibility when dividing data in Excel, whether you prefer using simple formulas, or manual calculations. Excel ensures you can easily perform division tasks for various purposes while avoiding potential errors.

Chapter 5.7 Average Function

Averaging data in Excel is a common task, and there are various ways to do it using formulas and functions. Here are three methods to calculate the average of Excel data:

1. Using the AVERAGE Function:

- What It Does: The AVERAGE function calculates the average of a range of numbers.
- Formula Syntax: =AVERAGE(range)
- Example: Let's say you have a list of numbers in cells B1 to B5, and you want to find the average of these numbers.

Steps:
1. Select the cell where you want the average to appear (e.g., B6).
2. Type `=AVERAGE(B1:B5)` and press Enter.
3. Cell B6 will display the average of the numbers in B1 to B5.

Figure 155

2. Using the AutoSum Button:

- What It Does: Excel provides a quick way to calculate the average using the AutoAverage button on the toolbar.
- Steps:
• Select the cell where you want the average to appear (e.g., B6).
• Click on the drop down button of AutoSum (Σ)
in the toolbar.
• Click on the Average

• Excel will automatically suggest a range to average based on nearby data. If the suggested range is correct, press Enter to apply the average.

3. Manually Calculating the Average:

- What It Does: You can manually calculate the average by adding all values and dividing by the count of values.
- Formula Syntax: =(number1 + number2 + ...) / count
- Example: Suppose you have numbers in cells B1 to B4, and you want to calculate the average manually.
Steps:

• Select the cell where you want the average (e.g., B6).
• Type =(B1 + B2 + B3 + B4) / 4 and press Enter.
• Cell B6 will display the manually calculated average.

Bonus Tip : To view the average, count, and sum for selected cell values, follow these steps: simply select the cells, and at the bottom of the Excel window, check the status bar for the displayed average, count, and sum.

These methods provide you with flexibility when calculating averages in Excel, whether you prefer using functions, the AutoAverage button, or manual calculations. Excel ensures you can easily obtain the average value you need for your data.

Chapter 5.8 Percentage Calculations

Calculating percentages in Excel is a common task, and there are various methods to do it. Here are three ways to perform percentage calculations in Excel, each explained step by step with examples:

Method 1: Using Basic Formulas

Step 1: Enter Data

- Start by entering the data you want to calculate a percentage for. For example, let's say you have a value in cell A1 (e.g., 500) and you want to calculate 10% of that value.

Step 2: Create a Formula

- In an empty cell where you want the result to appear (e.g., B1), enter the formula to calculate the percentage. The formula to calculate 10% of a value in cell A1 would be =A1 * 10%.

Step 3: Press Enter
- After entering the formula, press Enter. Cell B1 will now display the result, which is 50 (10% of 500).

Example

Let's say you have some student data in Excel. In Column A, you've got names, Column B has the marks obtained, and Column C shows the total marks. Now, if you want to calculate the percentage in Column D, here's a simple guide:

Figure 156
Steps:
• Click on the cell in Column D where you want the percentage (e.g.,

D2)
• In the D2 Cell, type =B2/C2 (this gives you the percentage in
decimals for the first student).

• Press Enter. Now, you've got the percentage for the first student, but in number format, not as a percentage.
• Copy the formula (the one you used in D2).
• Paste it in cells D3, D4, D5, and D6 (or however many students you have).

Now, here's the finishing touch to make it look even better:

• Select the cells D2 to D6 (where you used the formula).
• Go to the Home tab.
• Click on Percentage Style.

And there you go! Your percentages are calculated and formatted to look all neat(See Figure 158).
Figure 157
2. Click on % 1. Select the Cells
Selected Cells Converted in %

Chapter 5.9 Count Counta and Countblank

In Excel, the COUNT, COUNTA, and COUNTBLANK functions are used to count data in a range. Let's explore each of these functions with step-bystep.

Data Setup
Assuming you have the following data in Column B: Figure 159

COUNT Function

Let's start by using the COUNT function to count the number of cells that contain numerical values.
Step 1.1: Select a cell where you want the result. Let's choose C1. Step 1.2: Type the formula: =COUNT(B1:B5)
Step 1.3: Press Enter. The result will be: 3

Explanation: The COUNT function counts the cells in the range B1 to B5 that contain numerical values. In this example, cells B1, B2, and B5 have numbers, so the result is 3.

COUNTA Function

Now, let's use the COUNTA function to count the number of non-blank cells.
Step 2.1: Select a cell, let's say again C1.
Step 2.2: Type the following formula: =COUNTA(B1:B5)
Figure 161
Step 2.3: Press Enter.
The result will be: 4

Explanation: The COUNTA function counts all non-blank cells in the range B1 to B5. In this example, cells B1, B2, B4, and B5 are non-blank, so the result is 4.

COUNTBLANK Function

Finally, let's use the COUNTBLANK function to count the number of blank cells.
Step 3.1: Select cell, let's say C1.
Step 3.2: Type the following formula: =COUNTBLANK(B1:B5)
Figure 162
Step 3.3: Press Enter. The result will be: 1

Explanation: The COUNTBLANK function counts the number of blank cells in the range B1 to B5. In this example, only cell B3 is blank, so the result is 1.

These functions (COUNT, COUNTA, COUNTBLANK) are valuable tools in Excel for quickly analyzing and summarizing the composition of your data. They help you understand the distribution of numerical values and identify empty or non-empty cells in a range.

Chapter 5.10 Lower Proper and Upper Function

Let's explore the LOWER, PROPER, and UPPER functions in Excel along with examples:

1. LOWER Function:

- The LOWER function converts text to all lowercase letters. Syntax: =LOWER(text)
Example: Suppose cell A1 contains the text "Hello World." To convert it to lowercase, you would use the LOWER function like this:
=LOWER(A1)
The result will be hello world.

2. PROPER Function:

- The PROPER function capitalizes the first letter of each word in a text string and converts the rest to lowercase.
Syntax: =PROPER(text)
Example: If cell A1 contains the text "exCEL is sUper hERO," you can use the PROPER function like this:
=PROPER(A1)
The result will be Excel Is Super Hero.

3. UPPER Function:

- The UPPER function converts text to all uppercase letters. Syntax: =UPPER(text)
Example: If cell A1 contains the text "My Live Support," you can use the UPPER function like this:
=UPPER(A1)

The result will be MY LIVE SUPPORT.
Additional Tips:
- You can use these functions to clean up data or format it consistently.

- These functions are especially useful when dealing with text data from various sources that may not have uniform formatting.
Here's a table summarizing the functions and their effects:
Figure 163
These functions help ensure data consistency and make text easier to work with in Excel.

Chapter 5.11 Contactenate Function

The CONCATENATE function in Excel is used to combine or concatenate multiple text strings into one. It's a helpful tool when you want to join various pieces of text together. However, CONCATENATE is an older function, and a more modern alternative is to use the ampersand ( & ) operator. Let's explore how to use both the CONCATENATE function and the ampersand operator with step-by-step explanations.

1. Using CONCATENATE Function:
Syntax for CONCATENATE: =CONCATENATE(text1, text2, ...)
- `text1`, `text2`, ...: These are the text strings you want to combine. You can include up to 255 text strings.
Example: Creating Full Names

Suppose you have a list of first names in column A and last names in column B, and you want to create full names in column C. Just use this simple formula in the Column C, let's say C2:

=CONCATENATE(A2, " ", B2)
Figure 164

In cell C2 and below, we use the CONCATENATE function to combine the first name (`A2`) and last name (`B2`) with a space in between. To add space between two text strings, we include a space enclosed in double quotes (" ").

Copy the formula from cell C2 and paste it in cells C3 and C4 to create full names for other individuals.
Figure 165
2. Using the Ampersand (`&`) Operator (Modern Alternative):
The ampersand (`&`) operator is a more concise and commonly used method for text concatenation in Excel.
Syntax for Ampersand Operator:
=text1 & text2
- text1 and text2: These are the text strings you want to combine.
Example: Creating Full Names (Using Ampersand Operator)
You can achieve the same result using the ampersand operator to create full names.
Figure 166

In cell C2 and below, we use the ampersand operator (&) to combine the first name (A2) and last name (B2) with a space in between. To add space between two text strings, we include a space enclosed in double quotes (" ").

Copy the formula from cell C2 and paste it in cells C3 and C4 to create full names for other individuals.
Figure 167

Using the ampersand operator is a more concise and modern way to concatenate text in Excel. It allows you to join text strings with ease and readability while specifying the space you want between them.

Chapter 5.12 Value Function

The VALUE function in Excel is a powerful tool that can convert various types of text values into actual numerical values, including percentages and dates. It can also help remove commas from values.

Sometimes, when you copy data from a website, a document, or another source and paste it into Excel, the data might be treated as text, making it challenging to use for calculations.

Here's a step-by-step explanation of value function :

Converting Text to Numerical Value:

Sometimes, you may have numbers stored as text in your Excel worksheet. The VALUE function can help convert these text numbers into actual numerical values.

Step 1: Enter a Text Number
Let's start with an example. Suppose you have a text number in cell A1, like this:
Step 2: Use the VALUE Function
In cell B1, where you want the numerical value, enter the following formula: =VALUE(A1)
Step 3: Press Enter
Press the Enter key, and Excel will convert the text "123456" in cell A1 into a numerical value in cell B1.

Converting Percentage to Normal Value:

The VALUE function can also convert percentages stored as text into regular numerical values.
Step 1: Enter a Percentage Value
Let's say you have a percentage value in cell A1, like this:

Step 2: Use the VALUE Function
In cell B1, where you want the numerical value, enter this formula: =VALUE(A1)

Step 3: Press Enter
Press Enter, and Excel will convert the percentage 25% in cell A1 into the decimal numerical value 0.25 in cell B1.

Removing Commas from Values:

The VALUE function can also be used to remove commas from values, making them easier to work with.
Step 1: Enter a Value with Commas
Let's say you have a value with commas in cell A1, like this:

Step 2: Use the VALUE Function
In cell B1, where you want the value without commas, enter this formula: =VALUE(A1)

Step 3: Press Enter
Press Enter, and Excel will remove the commas from the value 1,075,000 in cell A1, leaving you with the numerical value 1075000 in cell B1.

The VALUE function is a handy tool for converting and manipulating different types of text values in Excel, making your data analysis and calculations more accurate and efficient.

Chapter 5.13 Text Function

The TEXT function in Excel is a powerful tool that allows you to format and display numbers, dates, and times in various ways. It's particularly handy for converting values into text with specific formats. Let's understand how the TEXT function works with an example:

Syntax:
=TEXT(value, format_text)
• Value: This is the numeric value, date, or time that you want to convert into text.

• Format_text: Here, you specify the format you want for the text result. This can include a combination of numbers, text, and special characters.

Example : Suppose you have a date in cell A1, like "15-01-23", and you want to display it as "January 15, 2023". You can use the TEXT function like this:

=TEXT(A1, "mmmm dd, yyyy")
This will convert the date into the specified format, and the result will be January 15, 2023.
Here's a table with a few more examples:
Figure 168
Figure 169

You can format numbers, dates, and times in a variety of ways using the TEXT function. It's a versatile tool to make your data more presentable and meaningful.

Chapter 5.14 Copying and Pasting Formulas

Copying and pasting formulas in multiple cells is a handy feature in Excel. You can achieve this using various methods such as dragging, pressing Ctrl + D, double-clicking, or copying and pasting from a cell. Here's a step-bystep explanation with examples:

Method 1: Dragging to Paste Formulas

1. Enter a Formula: Start by entering a formula in a cell. For example, in cell A1, you can enter `=B1+C1` to calculate the sum of values in cells B1 and C1.

Figure 170
2. Select the Formula Cell: Click on the cell containing the formula (in this case, A1) to select it.

3. Hover Over the Fill Handle: Move your cursor
to the bottom-right corner of the selected cell (you'll
see a small square, known as the "fill handle").

4. Drag the Fill Handle: Click and drag the fill handle down or across the cells where you want to paste the formula. As you drag, Excel will automatically copy and adjust the formula for each cell.

Figure 171

Example: If you drag the fill handle from cell A1 down to A5, Excel will paste the formula in cells A2, A3, A4, and A5, adjusting the cell references accordingly.

Method 2: Using Ctrl + D to Fill Down

1. Enter a Formula: Enter your formula in the first cell where you want to start the series. (e.g., A1)

2. Select the Formula Cell: Start by clicking on the cell that contains the formula you want to copy.
3. Choose the Destination Cells: Next, choose all the cells where you want to paste the formula. You can do this by clicking and dragging your mouse to select multiple cells.

Figure 172

4. Press Ctrl + D: Now, while the formula cell is still selected, press and hold the Ctrl key on your keyboard and then press the letter "D" (Ctrl + D). This action will copy the formula from the original cell and paste into all the selected cells.

Figure 173

Example: If you have a formula in cell A1 and you press Ctrl + D, Excel will paste the formula into cells A2, A3, A4, A5 and so on, filling down the column.

Method 3: Double-Clicking the Fill Handle

1. Enter a Formula: Enter your formula in the first cell where you want to start the series (e.g., A1).
Figure 174
2. Select the Formula Cell: Click on the cell containing the formula.

3. Double-Click the Fill Handle: Double-click the fill
handle (the small square at the bottom-right corner of the
selected cell). Excel will automatically copy and paste the
formula down until it encounters an empty cell or different

data.

Figure 175

Example: If you have a formula in cell A1 and you double-click the fill handle, Excel will paste the formula into cells A2, A3, A4, A5 and so on, stopping when it reaches an empty cell or different data.

Method 4: Copy and Paste formula from a Cell

Imagine you have a formula in cell A1, and you want to copy it to cells A2, A3, A4, and A5. Here's how you can do it:
Step 1: Copy the formula

• Click on cell A1 to select it.
• Right-click on cell A1 and choose "Copy" from the menu.
• Or, press Ctrl+C on your keyboard.

Figure 176
Step 2: Paste the formula

• Click on cell A2 to select it.
• Hold down the Shift key and click on cell A5 to select all the cells from A2 to A5.
• Right-click on any of the selected cells (A2, A3, A4, or A5) and choose "Paste" from the menu.
• Or, press Ctrl+V on your keyboard.
• Excel will copy the formula from cell A1 and paste it into all the selected cells (A2 to A5).

Figure 177

These methods make it easy to copy and paste formulas in multiple cells in Excel, saving you time and effort when working with large datasets or complex calculations.

Chapter 5.15 Subtotal Function

The SUBTOTAL function in Excel is used to perform various calculations on a range of data while ignoring other SUBTOTAL functions within that range. It's a versatile function often used for creating subtotal rows in tables. Let's understand how to use the SUBTOTAL function step by step, along with its uses and benefits.

Syntax for SUBTOTAL Function:
=SUBTOTAL(function_num, range1, [range2], ...)

- function_num : This is a numerical code that represents the type of calculation you want to perform. You can choose from various functions (e.g., 1 for AVERAGE, 2 for COUNT, 3 for MAX, etc.).

- range1, [range2], ...: These are the ranges you want to include in the calculation. You can provide multiple ranges if needed.
Uses and Benefits of Using SUBTOTAL Function:
1. Creating Subtotals in Tables: SUBTOTAL is commonly used to create subtotal rows in tables, making it easier to summarize and analyze data.

2. Dynamic Subtotals: Unlike static values, SUBTOTAL provides dynamic subtotals that adjust automatically when you filter or change the data. 3. Ignore Hidden Rows: SUBTOTAL can ignore rows that are hidden due to filters, allowing you to calculate subtotals for visible data only.

Example: Calculating Subtotals in a Sales Table

Let's say you have a table of sales data with columns for Salesperson, Product, Quantity Sold, and Revenue. You want to calculate subtotal revenue for each salesperson.

Figure 178
In cell D8, you can use the SUBTOTAL function to calculate the subtotal revenue: =SUBTOTAL(9, D2:D7)

Explanation:
- `9` represents the SUM function.
- `D2:D7` is the range containing revenue data.

After using the SUBTOTAL function, follow these simple steps to apply filters and see dynamic subtotals:
1. Select Your Data: Click on any cell within your data table.

2. Apply Filter: Press Ctrl + Shift + L on your keyboard. This action applies a filter to your data, allowing you to interactively sort and view specific portions.

3. Explore Filtered Data and Subtotals: Once the filter is applied, you can filter your data based on specific criteria. The SUBTOTAL function will dynamically adjust to show subtotals for the selected Salesperson. Simply explore and enjoy a tailored view of your data with live subtotals!

Bonus Tip:

• You can experiment with different function_num values (e.g., 1 for AVERAGE, 4 for MAX) to perform various calculations with SUBTOTAL.

• While using subtotal function, you can write the function number like 9 for sum, 1 for average or you can double click on the function number.

Figure 181

The SUBTOTAL function is valuable for creating dynamic subtotals and summaries in Excel, especially when working with large datasets or tables that require frequent updates. It ensures that your subtotals adapt to changes in your data, saving you time and reducing the risk of errors.

Chapter 5.16 Subtotal Function Number Difference

In the SUBTOTAL function, each calculation function has two corresponding function numbers: one for including hidden (filtered) rows and one for ignoring hidden rows. The difference between these function numbers is crucial for controlling whether or not hidden rows are considered in the calculation. Here's a explanation:

Function Numbers for SUBTOTAL:
- 1 to 11: These function numbers include hidden rows in the calculation.
- 101 to 111: These function numbers ignore hidden rows and calculate only on visible (unfiltered) rows.
Benefit of Using Higher Function Numbers (101 to 111):

When you use higher function numbers (101 to 111), you ensure that the SUBTOTAL calculation only includes visible (unfiltered) rows. This can be especially useful when you're working with filtered data, as it provides more accurate results for the visible data.

Examples:

Let's use the same sales data table as in the previous example. This time, we want to calculate the average revenue for Alice and Bob, considering only visible (unfiltered) rows.
In cell D8, you can use the SUBTOTAL function with function number 101 to calculate the average revenue: =SUBTOTAL(101, D2:D7)

Figure 182
Explanation:
- `101` represents the AVERAGE function while ignoring hidden rows.
- `D2:D7` is the range containing revenue data for Alice and Bob.

Now, if you apply a filter to the table to hide Carol's data, the average revenue in cell D8 will adjust automatically to consider only the visible rows for Alice and Bob.

Figure 183

The benefit of using higher function numbers (101 to 111) is that your calculations remain accurate even when you filter or hide rows, providing you with dynamic and reliable results based on the visible data.

Chapter 5.17 Goal Seek Function

The Goal Seek function in Microsoft Excel is a powerful tool that allows you to find a desired result by adjusting one input value. It's like working backward to achieve a specific goal. Let's understand how Goal Seek works.

Example : Calculating a Required Sales Target

Suppose you have a business and want to find out how many units you need to sell to reach a revenue target of $10,000. You know the price per unit is $50, but you're not sure how many units you need to sell. Here's how to use Goal Seek:

1. Set up your table:
Target Revenue: $10,000
2. In cell B2, you want to calculate the number of units needed to reach the target revenue. Enter this formula in cell C2:
=B2 * A2
3. Now, go to the " Data" tab in the Excel ribbon and
select "What-If Analysis" > "Goal Seek."

4. In the Goal Seek dialog box:
- Set "Set Cell" to C2 (the cell containing your revenue formula).
- Set "To Value" to 10,000 (your target revenue).
- Set "By Changing Cell" to B2 (the cell with the number of units).

Figure 184
Figure 185
5. Click "OK." Excel will automatically adjust the number of units in cell B2 to meet the 10,000 revenue target.

Goal Seek is a valuable tool in Excel for solving various what-if scenarios. It helps you find the necessary input values to achieve a specific goal or result, making it a handy tool for financial planning, forecasting, and decision-making.

Chapter 5.18 Aggregate Function

Aggregate function in Excel is a powerful tool for performing various calculations on your data. It allows you to apply multiple functions, such as SUM, AVERAGE, MAX, MIN, and more, to a range of values. This function comes in handy when dealing with data that may contain errors or unwanted values, as it can ignore or exclude these values from calculations.

Here's how to use the AGGREGATE function with a step-by-step explanation and examples:
Syntax:
=AGGREGATE(function_num, options, ref1, [ref2], ...)
• function_num: Specifies the function to be used for the calculation (e.g., 1 for AVERAGE, 3 for COUNTA, 9 for MAX, etc.).
• options: Determines which values to ignore or include (e.g., 1 to ignore hidden rows, 4 to ignore error values, etc.).
• ref1, [ref2], ...: The ranges or references containing the data you want to analyze.
Function Number
Options Number

Example 1 - AVERAGE with Error Handling:

Suppose you have a dataset in column B, and some cells contain errors or unwanted values.
You want to calculate the average of these values while ignoring errors. Here's the formula:

=AGGREGATE(1, 6, B1:B6)
• function_num 1 : Represents the AVERAGE function.
• options 6: Ignores error values.
• ref1 B1:B6: The range containing the data.

The result will be the average of valid numbers: (5 + 7 + 9) / 3 = 7.

Example 2 - SUM with Hidden Rows:

Suppose you have data in column B, with some rows hidden.
You want to calculate the sum of these values while ignoring the hidden row. Here's the formula:

=AGGREGATE(9, 5, B1:B5)
• function_num 9 : Represents the SUM function.
• options 5: Ignores hidden rows.
• ref1 B1:B5: The range containing the data.

Figure 186
The result will be the sum of visible rows: 10 + 20 + 30 + 40 = 100.

Aggregate function offers versatility and control, making it a valuable tool for working with data in Excel. It allows you to perform calculations while handling errors and hidden data effectively.

Chapter 5.19 Trim Function

The TRIM function in Excel is handy for removing extra spaces from text. Let's understand, how it works using an example:
Suppose you have a list of names, and some of them have unnecessary spaces:
Figure 187
You want to clean up these names and remove the extra spaces.
Here's how to use the TRIM function:
1. In cell B2, where you want the cleaned-up name to appear, enter the following formula:
=TRIM(A2)
This formula takes the text from cell A2 and removes extra spaces. The result in cell B2 will be:

• Cleaned Name (B2): "John Smith"
2. Drag this formula down to apply it to cells B3, B4 and B5. After dragging the formula, your list of cleaned names will look like this:

• Cleaned Name (B3): "Jane Doe"
• Cleaned Name (B4): "Alice Johnson"
• Cleaned Name (B5): "Mark Anderson"

Figure 188
Figure 189

The TRIM function helps you eliminate excessive spaces and format text neatly, which can be especially useful when working with datasets containing inconsistent spacing.

Chapter 5.20 Len Function

The LEN function in Excel allows you to determine the number of characters in a text string. Here's how you can use it to validate Indian mobile numbers with ten digits:

Suppose you have a list of mobile numbers in column A like this:
Figure 190
You want to check if each mobile number has exactly ten digits and identify those that do not.
Here's how to use the LEN function:
1. In cell B1, where you want to display the character count for the mobile number in A1, enter the following formula:
=LEN(A1)
This formula calculates the number of characters in cell A1. 2. Drag the formula down to apply it to cells B2, B3, B4, and B5.
Now, in column B, you will see the character count for each mobile number:
Figure 191

Conditional Formatting in this example :

To identify the mobile numbers with the correct ten digits, you can use Excel's Conditional Formatting feature:
1. Select cells in column B that contain the character counts (B1 to B5).
Figure 192
2. Go to the "Home" tab in Excel.
3. Click on "Conditional Formatting" in the "Styles" group.
4. Choose " New Rule."
5. Select "Use a formula to determine which cells to format."
6. Enter the formula: =B1<>10
Figure 193

7. Click on the "Format" button and choose the formatting style for incorrect mobile numbers. For example, you can change the Fill color to Yellow.

8. Click "OK" to apply the rule.

Now, any mobile number with a character count other than 10 will be visually highlighted. This allows you to easily identify and rectify mobile numbers with the wrong number of digits.

Figure 194
Using the LEN function along with Conditional Formatting is a handy way to validate data in your Excel spreadsheets.
Tip - Explore the Chapter 3.5 on conditional formatting to learn more about this feature.

Chapter 5.21 ABS Function

The ABS function in Excel is used to return the absolute value of a number, which is essentially the number without its sign. Here's how you can use it: Function Syntax: =ABS(number)
• number: This is the numeric value for which you want to find the absolute value.
Example:

Suppose you have a table in Excel with numbers in column A, and you want to find the absolute values of these numbers in column B. Here's how you can do it:

Figure 195
1. In cell B1, where you want to display the absolute value for the number in A1, enter the following formula: =ABS(A1)

This formula calculates the absolute value of the number in cell A1. 2. Drag the formula down to apply it to cells B2, B3, and B4.

After applying the ABS function, your table will look like this (see figure 196)
Figure 196

As you can see, the ABS function removes the negative sign from negative numbers, leaving only the positive values as well as keeping positive numbers unchanged.

In this way, you can use the ABS function to calculate the absolute value of numbers in your Excel spreadsheet. It's a useful function when you need to work with the magnitude of values, irrespective of their sign.

Chapter 5.22 Rank Function

The RANK function in Excel is used to determine the rank of a specific number within a range of numbers. It can be handy for finding the position of a value in a list, whether you want to know the highest, lowest, or any other position based on your criteria. Here's how to use it :

Function Syntax: =RANK(number, ref, [order])
• number: This is the number for which you want to find the rank.

• ref: This is the reference range of numbers where the rank is determined.

• [order]: This is an optional argument. If you want to rank in ascending order (smallest to largest), you can use 0 or omit this argument. If you want to rank in descending order (largest to smallest), use 1.

Example:
Let's say you have a list of test scores in Excel in column A, and you want to find the rank for each student's score. Here's how you can do it: Figure 197

Step 1 :

In cell C2, where you want to display the rank for the number in B2, enter the following formula:
=RANK(B2, $B$2:$B$6)
This formula calculates the rank of the number in cell B2 within the range of numbers in B2 to B6.
After choosing the Reference Range in the formula, simply press

theF4key on your keyboard to lock or freeze the selected range.

Step 2 :

Drag the formula down to apply it to cells C3, C4, C5 and C6. After applying the RANK function, your table will look like this:
Figure 198

In this example, we calculated the rank of each test score, and the result in column C shows the position of each score within the range. For instance, a score of 95 ranks first because it's the highest, while a score of 78 ranks fifth, being the lowest in the range.

The RANK function is useful when you need to determine the relative position of a value compared to others in a list, which can be essential for tasks like ranking students by their test scores.

Chapter 5.23 PMT Function

The PMT function in Excel is used to calculate the monthly payment for a loan or investment based on a fixed interest rate, the number of payments, and the principal amount. It's a helpful tool for financial planning and analysis. The syntax for the PMT function is as follows:

PMT Formula Syntax
=PMT(rate, nper, pv, [fv], [type])
• rate: The interest rate for each period.
• nper: The total number of payment periods.
• pv: The present value, or the total loan amount.

• [fv]: (Optional) The future value or a cash balance after the last payment. It's usually omitted or set to 0.
• [type]: (Optional) The payment type, either 0 (end of the period) or 1 (beginning of the period). It's usually omitted or set to 0.
Example : Car Loan

Suppose you're considering a car loan. You want to know the monthly payment for a $20,000 loan with a 4% annual interest rate to be paid off over 4 years (48 monthly payments).

• rate (annual interest rate) = 4%
• nper (total number of payments) = 48 (4 years x 12 months)
• pv (loan amount) = -$20,000 (negative since it's a loan)
• fv (future value) = 0 (no remaining balance)
• type (payment type) = 0 (end of the period)

Step 1: Set up your Excel worksheet with these values:
Figure 199
Step 2: In another cell (e.g., C1), enter the PMT function: =PMT(B1/12,B2,B3,B4,B5)
The interest rate is divided by 12 to get the monthly rate (4% annual rate / 12 months).
Figure 200
Step 3: Press Enter. The result in C1 will be the monthly car loan payment, which is approximately $451.58. (see figure 201)
Figure 201

Using the PMT function, you can quickly calculate loan payments for different scenarios, such as car loans, mortgages, and more, which is valuable for budgeting and financial planning.

Chapter 5.24 Date and Time Function

Date and time functions are essential in Excel for various tasks. Here are some of the most important date and time functions :

1. TODAY() Function:

The TODAY() function returns the current date.
How to use:
In a cell (e.g., B1) type =TODAY() and press Enter.

2. NOW() Function:

The NOW() function returns the current date and time.
How to use:
In a cell (e.g., B1) , type =NOW() and press Enter.
Result:

3. DATE() Function:

The DATE() function creates a date using year, month, and day values.
How to use:
In a cell (e.g.,D2), type =DATE(year, month, day) and replace year, month, and day with the desired values.
In cell D2, enter =DATE(A2, B2, C2) to create a date from the provided values.
Result:

4. TIME() Function:

The TIME() function creates a time using hours, minutes, and seconds.
How to use:
In a cell (e.g.,D2) type =TIME(hours, minutes, seconds) and replace hours, minutes, and seconds with the desired values.
Result:
These date and time functions are fundamental in Excel and can be used in

Part VI: Advanced Formulas and Functions

Chapter 6.1 Vlookup Function

VLOOKUP, short for "Vertical Lookup," is an incredibly useful function in Excel that helps you find and retrieve specific data from a table based on a reference value. It's like a digital filing system for your data.

Benefits of Using VLOOKUP:

• Quick Data Retrieval: VLOOKUP allows you to find information in large datasets swiftly.
• Accuracy: It reduces the chances of manual errors when searching for data.
• Dynamic: You can update your reference value, and VLOOKUP will instantly fetch the updated data.
• Structured Data: Ideal for databases or tables with organized rows and columns.

Limitations of VLOOKUP:

• Exact Match Requirement: By default, VLOOKUP requires an exact match for the lookup value. If there's no exact match, it won't work.
• Left-to-Right: It searches for data from left to right in a table. If the data you need is to the left of your reference value, you might encounter issues.
• Single Column Retrieval: VLOOKUP can only retrieve data from one column at a time.

How to Use VLOOKUP:

Step 1: Understand the Function Syntax:
The basic syntax of VLOOKUP is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

- lookup_value : The value you want to find.
- table_array : The table where you want to search.
- col_index_num : The column number from which to retrieve data.

- [range_lookup] : Optional. TRUE for an approximate match (default), FALSE for an exact match.
Step 2: Set up Your Data:

Ensure your data is organized in a table with columns. Let's say you have a table with names in column A and corresponding phone numbers in column B.

Figure 202
Step 3: Apply VLOOKUP:
Let's say you want to find the phone number for a specific name (e.g., "John"). In the cell D1, you can use the following formula:
=VLOOKUP("John", A1:B5, 2, FALSE)
Figure 203

- "John" is your lookup value.
- A1:B5 is your table.
- 2 indicates you want data from the second column (phone numbers).
- FALSE specifies an exact match.

Step 4: Enjoy the Result:
Figure 204

Using the VLOOKUP formula explained earlier, if you input =VLOOKUP("John", A1:B5, 2, FALSE), Excel will return 111213 because "John" corresponds to that phone number.

Bonus Tip : You can use VLOOKUP with 0 for FALSE and 1 for TRUE

Multiple Lookup Values

You can get multiple results by having multiple lookup values in different cells and then copying the formula. Here's a step-by-step example: Scenario: You have a list of products and their prices, and you want to find the prices of multiple products using VLOOKUP.
Step 1: Set Up Your Data:
Figure 205

Step 2: Enter Your Lookup Values:

- In cell D2, type "Apple."
- In cell D3, type "Orange."
- In cell D4, type "Guava."

Step 3: Write the VLOOKUP Formula in E2:
In cell E2, enter the following formula to find the price of the first product (Apple):
=VLOOKUP(D2, $A$1:$B$8, 2,0)
Figure 206
Note In the formula, we used '0' for the range lookup argument to represent False.
This formula looks up "Apple" in your data table, finds its price, and displays it in cell E2.
Absolute Reference for Table Array:
• After completing the formula in cell E2, select the table array (A1:B8) within the formula.
• Press the F4 key on your keyboard to make the table array an absolute reference (e.g., $A$1:$B$8).
Step 4: Copy the Formula to Cells E3 and E4:

• Double click on the small square at the bottom
right corner of the selected cell E2 (the fill handle)

for paste the same formula in below cells.

Figure 207

Results:
• Cell E2 will display the price of Apple (150).
• Cell E3 will display the price of Orange (60).
• Cell E4 will display the price of Guava (95).

Note : When using VLOOKUP in Excel:
• Your lookup value (what you're searching for) should be on the left side of your data table.

• The data you want to retrieve should be on the right side of your data table. This ensures that VLOOKUP functions correctly, like an index in a book guiding you to the right page. If your data is on the left of your lookup value, it can lead to issues.

Chapter 6.2 Hlookup Function

HLOOKUP, or Horizontal Lookup, is a useful Excel function that allows you to search for a value in the first row of a range (table or array) and return a value in the same column from a specified row. It's like looking across a row to find information.

Benefits of Using HLOOKUP:

- Horizontal Search: HLOOKUP is used for horizontal searches. It looks for a specified value in the first row of a table and retrieves a corresponding value from a row below it.

- Simplifies Data Retrieval: HLOOKUP simplifies data retrieval when you have a table organized in rows and need to extract data based on a known value in the top row.

- Flexible: It allows you to search for an approximate match (TRUE) or an exact match (FALSE).

Limitations of HLOOKUP:

- Horizontal Data Only: HLOOKUP can only search horizontally, so it's suitable for tables where data is arranged in rows.

- Limited to Top Row: It searches for the lookup value in the first row of the table and retrieves data from a row beneath it. If the lookup value isn't in the top row, it won't work.

- Exact Match Required: By default, HLOOKUP requires an exact match. If you need an approximate match, you may need to use other functions or manipulate your data.

How to Use HLOOKUP in Excel:

Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

- lookup_value : The value you want to find in the top row of the table.
- table_array : The range where you want to search for the value.
- row_index_num : The row number from which to retrieve the data.

- [range_lookup] : Optional. TRUE (approximate match) or FALSE (exact match). You can use 0 for FALSE and 1 for TRUE in the range lookup to customize how HLOOKUP searches for your data.

Example:

Let's say you have a table of fruit prices organized horizontally, and you want to find the price of "Banana."
Data Table:
Figure 208
HLOOKUP Formula:
In cell B4, you can use the following HLOOKUP formula to find the price of "Banana":
=HLOOKUP("Banana", A1:F2, 2, 0)
Figure 209

- "Banana" is the lookup value.
- A1:F2 is the table range.
- 2 specifies the row number (Price row).

- 0 ensures an exact match. You can use 0 for FALSE and 1 for TRUE in the range lookup
Figure 210
The result will be 100, which is the price of a Banana.
HLOOKUP helps you retrieve data efficiently when your table is organized horizontally and your lookup value is in the top row.

Chapter 6.3 Xlookup Function

XLOOKUP Function in Excel: A Powerful Replacement for VLOOKUP and HLOOKUP

The XLOOKUP function in Excel is a versatile and powerful lookup function that simplifies the process of finding and retrieving data from a table or range. It offers several advantages over traditional lookup functions like VLOOKUP and HLOOKUP, including the ability to perform both vertical and horizontal lookups, handle multiple criteria, and return arrays of values. Let's explore XLOOKUP in detail:

Advantages of XLOOKUP:

1. Bidirectional Lookup: Unlike VLOOKUP, which only searches from left to right, XLOOKUP can perform both vertical and horizontal lookups. It can search in any direction, making it more flexible.

2. Multiple Criteria: XLOOKUP can handle multiple criteria, allowing you to perform complex lookups with ease. This is especially helpful when working with databases with multiple conditions.

3. Handling Errors: XLOOKUP handles errors gracefully. You can specify custom error messages or return blank cells when a lookup value isn't found, improving data accuracy.

4. Return Arrays: XLOOKUP can return arrays of values, which is useful for returning multiple results based on a single lookup condition. 5. No More Column Index: Unlike VLOOKUP, you don't need to count columns to specify the column index. XLOOKUP uses a simplified syntax that specifies the lookup value and the array to search.

XLOOKUP Formula Syntax:
The XLOOKUP function has a straightforward syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value : The value you want to find.
- lookup_array : The range or array where Excel should look for the `lookup_value`.
- return_array : The range or array from which you want to return a value.
- [if_not_found] : An optional argument to specify what to return if no match is found (e.g., an error message or a custom text).

- [match_mode] : An optional argument that specifies how closely the lookup value should match the data. You can use `1` for an exact match (default), `-1` for less than, and `2` for greater than.

- [search_mode] : An optional argument that determines the search direction. You can use `1` for the first-to-last search (default) or `-1` for the last-to-first search.

XLOOKUP Examples: Let's illustrate the power of XLOOKUP with some examples:
Example 1 - Basic Lookup:

Suppose you have a table with employee names in column A and their employee ID in Column B and their salaries in column C. You want to find the salary of an employee (Pramod) based on their name. You can use XLOOKUP as follows:

Figure 211
In cell E1, you can use the following XLOOKUP formula to find the salary of Pramod :
=XLOOKUP("Pramod", A1:A10, C1:C10)
Figure 212
This formula will return Pramod's salary which is 32704.

Example 2 - Finding an Employee's Department

Suppose you have a list of employees and their respective departments:
Figure 214

Now, you want to find the department of an employee using the XLOOKUP function. However, you also want to handle cases where the employee is not found. Let's say you want to display "Not Found" in such cases.

In cell D2, where you want the result, you can enter the following XLOOKUP formula:
=XLOOKUP("FRANK", A2:A6, B2:B6, "Not Found")

Explanation:
• "Frank" is the lookup value (employee name) you want to find.
• A2:A6 is the lookup array (range containing employee names).
• B2:B6 is the return array (range containing departments).

• "Not Found" is the value to be returned if the lookup value is not found.
Now, let's break down the possibilities:
1. If "Frank" is found in the lookup array (A2:A6), the formula will return the corresponding department.
2. If "Frank" is not found, the formula will return "Not Found" as specified in the "if_not_found" argument.
This way, you can gracefully handle situations where the lookup value doesn't have a direct match, providing a clear indication in the result.
Note :

XLOOKUP is supported in Microsoft Excel for Microsoft Office 365 , Microsoft Office 2021 and Excel for the Web. It's not available in older versions like Excel 2019, 2016, 2013, 2010.

Chapter 6.4 Unique Function

The UNIQUE function in Excel is used to extract unique values from a range or array, removing any duplicates. It's a handy tool when you want to get a list of distinct items from a larger dataset. Let's understand the UNIQUE function with a example:

Syntax:
=UNIQUE(range, [by_col], [exactly_once])
- range : This is the range or array from which you want to extract unique values.

- [by_col] (optional): This argument specifies whether you want to extract unique values by columns (TRUE) or by rows (FALSE). By default, it's set to TRUE.

- [exactly_once] (optional): This argument, when set to TRUE, returns only the values that appear exactly once in the source data. By default, it's set to FALSE.

Example: Unique Cities and Populations
Suppose you have a table with cities and their populations and you want to extract the unique city names with their population.
Figure 216
In cell D1, you can enter the following UNIQUE formula: =UNIQUE(A1:B7)
Figure 217

Explanation:

• A1:B7 is the range containing the table with cities and populations.

• The UNIQUE function will return an array of rows with unique combinations of city and population.
When you enter the formula and press Enter, cell D1 will display:

Figure 218
Now, the values in columns D and E represent the unique city-population pairs from the original table.

You can adjust the formula based on your specific requirements, such as extracting unique values by rows, columns, or finding values that appear exactly once.

Chapter 6.5 Filter Function

The FILTER function in Excel allows you to extract data from a range based on specific criteria. Here's an explanation of each part of the FILTER formula:

Formula Syntax : =FILTER(array, include, [if_empty])
- array : This is the range of data you want to filter. It could be a single column or multiple columns. This is where you specify the source data.

- include : This is where you define the conditions for filtering. It's an array of TRUE and FALSE values, with each value corresponding to a row in the `array`. Rows with TRUE values are included in the filtered result, while FALSE values are excluded.

- [if_empty] : This is an optional argument. You can specify what to display if no rows meet the filtering criteria. If you leave it blank, Excel will display an empty cell.

Example 1 : Filtering Sales Data

Suppose you have a table of sales data with columns for Product, Salesperson, and Revenue:
Figure 219

Now, let's say you want to filter the data to show only the sales made by Alice. In cell E1, where you want the filtered results, you can use the FILTER function:

=FILTER(A2:C7, B2:B7="Alice")
Figure 220

Explanation:
• A2:C7 is the range containing the entire sales table.

• B2:B7="Alice" is the condition. This part of the formula filters the data where the Salesperson column equals "Alice."
When you enter the formula and press Enter, cell E1 will display:

Figure 221
Now, you have a filtered table showing only the sales made by Alice.
Benefits of FILTER Function:
1. Dynamic Filtering: The FILTER function provides dynamic results. If you add or change data, the filtered results adjust automatically. 2. Multiple Conditions: You can use multiple conditions in the FILTER function to create more complex filters.
3. Easy to Use: The syntax is straightforward, making it easy to filter data based on specific criteria.

Example 2: Filtering by Revenue

Let's expand the example to filter data based on revenue. In cell E1, enter the following FILTER formula to show sales with revenue greater than $1,000:

=FILTER(A2:C7, C2:C7>1000)
Figure 222

Explanation:
• A2:C7 is the range containing the entire sales table.

• C2:C7>1000 is the condition. This part of the formula filters the data where the Revenue column is greater than $1,000. When you enter the formula and press Enter, cell E1 will display:
Figure 223
Now, you have a filtered table showing only the sales with revenue greater than $1,000.

This is a simplified example, but you can apply more complex conditions using logical operators (AND, OR) or combining multiple criteria in the `include` argument to create advanced filters.

Chapter 6.6 Minifs and Maxifs Functions

In Excel, there isn't a built-in MINIF or MAXIF function, but you can achieve similar results using the MINIFS and MAXIFS functions. These functions allow you to find the minimum and maximum values based on specified criteria. Let’s understand Minifs and Maxifs functions :

MINIFS Function:

MINIFS finds the minimum value in a range that meets multiple criteria.
Formula Syntax:
=MINIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

- range : The range of values you want to evaluate for the minimum.
- criteria_range1 : The first range containing criteria.
- criteria1 : The first criterion.
- criteria_range2, criteria2, ...: Additional ranges and criteria (optional).

Example: Analyzing Sales Data

Suppose you have a table containing sales data for different products, regions, and months:
Figure 224
Now, let's say you want to find the minimum and maximum sales for Laptops in the North region.
In separate cells (e.g.,F1), you can use the MINIFS and MAXIFS functions:
MINIFS Function for Minimum Sales:
=MINIFS(D2:D6, A2:A6, "Laptop", B2:B6, "North")

Explanation:
• D2:D6 is the range containing sales data.
• A2:A6, "Laptop" is the condition for product being "Laptop."
• B2:B6, "North" is the condition for region being "North."

When you enter the formula and press Enter, you'll get the minimum sales (110) for Laptops in the North region.
Figure 225
MAXIFS Function for Maximum Sales:
=MAXIFS(D2:D6, A2:A6, "Laptop", B2:B6, "North")

Explanation:
• D2:D6 is the range containing sales data.
• A2:A6, "Laptop" is the condition for product being "Laptop."
• B2:B6, "North" is the condition for region being "North."

When you enter the formula and press Enter, you'll get the maximum sales (120) for Laptops in the North region.
Figure 226

These examples demonstrate how to use the MINIFS and MAXIFS functions in Excel to find the minimum and maximum values based on multiple criteria, making your data analysis tasks more efficient.

Benefits of MINIFS and MAXIFS Functions:
1. Conditional Analysis: Easily find the minimum or maximum values based on specific conditions.
2. Multiple Conditions: Apply multiple conditions to narrow down your analysis.
3. Dynamic Results: The functions adapt automatically when you update or add new data.

These functions are like detectives helping you uncover insights in your data. Whether you're managing sales figures, tracking inventory, or analyzing any numerical data, MINIFS and MAXIFS are your go-to tools for targeted analysis.

Chapter 6.7 Sumif Function

The SUMIF function in Microsoft Excel is used to sum values based on a specific condition or criteria. It allows you to add up numbers that meet a given condition. Let's understand how to use SUMIF function.

SUMIF Syntax:
=SUMIF(range, criteria, [sum_range])

- range: This is the range of cells where Excel will apply the condition.
- criteria: It's the condition you want to specify.

- [sum_range] (optional): This is the range of cells that Excel will sum if they meet the condition.

Example: Monthly Sales Analysis

Suppose you have a table with monthly sales data for different products:
Figure 227
Now, let's say you want to find the total sales for the "Laptop". In a separate cell (e.g.,E1), you can use the SUMIF function:

Explanation:
• A2:A6 is the range containing product names.
• "Laptop" is the condition for the product being "Laptop."
• C2:C6 is the range containing sales amount.

When you enter the formula and press Enter, you'll get the total sales (230) for Laptops.
Figure 228

In summary, the SUMIF function simplifies your data analysis by allowing you to sum values selectively based on conditions, making it a user-friendly tool for various scenarios.

Chapter 6.8 Sumifs Function

The SUMIFS function in Microsoft Excel is used to sum values in a range based on multiple criteria or conditions. It allows you to add up numbers that meet specific criteria simultaneously. Let's understand how to use SUMIFS function :

SUMIFS Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range: This is the range of cells that you want to add up (sum) based on your specified conditions.
- criteria_range1: This is the first range where you set your first condition.
- criteria1: This is the first condition you want to apply to criteria_range1. Excel will sum the values from sum_range only if they meet this condition.

- [criteria_range2, criteria2] (optional): You can add more pairs of criteria ranges and conditions. Excel will sum the values from sum_range that meet all specified conditions.

Example: Sales Data

Let's say you have a table with sales data,
and you want to find the total sales for a
specific product and region.

Total Sales for Product A in the North Region
In cell E3, you want to find the total sales for Product A in the North region. =SUMIFS(C2:C6, A2:A6,"A",B2:B6,"North")

Explanation:
• C2:C6 is the range of sales.
• A2:A6 is the range of products with the condition for Product A.
• "A" is the condition for the Product A.

• B2:B6 is the range of regions with the condition for the North region.

• "North" is the condition for the North region.

Now, let's break down the calculation:

• The formula looks for rows where Product is "A" and Region is "North."
• It finds two rows that satisfy both conditions: Product A in the North region with corresponding sales values of 100 and 120.
• Therefore, the result of the formula would be the sum of these sales values: 100 + 120 = 220.
So, the result for Product A in the North region is 220.

Chapter 6.9 Absolute Reference

Absolute references are a key concept in Microsoft Excel, and they play a crucial role in ensuring that your formulas work as intended. Let's explore absolute references.

What is an Absolute Reference?

An absolute reference is a way to fix a specific cell or range in your Excel formula so that it doesn't change when you copy the formula to other cells. It's like anchoring a cell's location, making it a constant point of reference.

Why Use Absolute References?

Imagine you have a formula that involves a cell or a range, and you want to apply the same formula to multiple cells. Absolute references are essential when you want certain elements of the formula to remain constant while allowing others to adjust relative to their new location.

The Benefit of Absolute References:

Absolute references allow you to keep certain parts of your formula fixed, which is especially useful when dealing with large datasets or when you want to maintain a constant point of reference. They ensure that Excel always looks at the same cell or range, no matter where you copy the formula.

Keyboard Shortcut:
Press the F4 button on your keyboard to use the shortcut for Absolute Reference.

Absolute References Examples

Absolute references can be handy in various scenarios. Let's understand with some examples :

Example 1 : Adding a Fixed Value to Multiple Cells Suppose you have a list of prices in cells A1 to A5, and you want to increase each of them by a fixed amount, say $50, which is mentioned in cell C1.

Figure 231

Here's how you can do it: 1. Enter the Formula in B1: In cell B1, enter the formula =A1+$C$1. This formula adds the value in cell C1 (which is $50) to the price in cell A1. 2. Press Enter: After entering the formula, press Enter on your keyboard. 3. Copy the Formula Down: • Double Click on the bottom right corner of cell B1 (the small square known as the fill

handle).

Figure 232

You'll notice that the value in cell C1 ($50) remains fixed in all the formulas, but the cell references in column A adjust accordingly.
Example 2: Using Absolute Reference in SUMIF Formula

Scenario: You have sales data with product names in Column A (from A2 to A8) and corresponding sale amounts in Column B. In Column E, you want to summarize the total sales amount for each product.

Suppose you have sales data as follows:

Steps:
Step 1: Set Up Criteria
1. In Cell D2, type "Laptop." 2. In Cell D3, type "Tablet." 3. In Cell D4, type "Smartphone."

Figure 233
Step 2: Write SUMIF Formula in E2
1. In Cell E2, enter the formula: =SUMIF(A2:A8,D2,B2:B8)
Figure 235

• Explanation: This formula calculates the total sales amount for the product mentioned in D2 (Laptop) by summing the corresponding amounts in B2:B8.

Step 3: Make the Range Absolute
1. Select the Range (A2:A8): select the range A2:A8 within the formula.
2. Press F4 to Make it Absolute: Press the F4 key on your keyboard. This adds dollar signs to make the range absolute (e.g., $A$2:$A$8). 3. Result: Your formula in E2 should now look like
=SUMIF($A$2:$A$8, D2, B2:B8)
Step 4: Make the SUM Range Absolute
1. Select the SUM Range (B2:B8): select the SUM Range B2:B8 within the formula.
2. Press F4 to Make it Absolute: Press the F4 key again. This adds dollar signs to make the SUM range absolute (e.g., $B$2:$B$8). 3. Result: Your formula now looks like
=SUMIF($A$2:$A$8, D2, $B$2:$B$8)
Step 5: Press Enter
Press Enter to calculate the total amount for the specified product. Cell E2 now shows the total sales (1100) for the "Laptop."

Step 6: Copy and Paste the Formula
• Copy the formula from cell E2.

• Paste it in cells E3 and E4 using various methods (e.g., doubleclicking the fill handle or using Ctrl+C and Ctrl+V).
• The formula adjusts automatically for Tablet and Smartphone, calculating their total amounts.
Step 7: Understand the Result
Now, cells E3 and E4 show the total sales for "Tablet" and "Smartphone" respectively.
Figure 237

Results:
• Cell E2 will display the Total Amount of Laptop (1100).
• Cell E3 will display the Total Amount of Tablet (980).
• Cell E4 will display the Total Amount of Smartphone (800).

Bonus Tip Why we use absolute references for the range and sum range but not for the criteria:
Absolute Reference for Range and Sum Range:
• Reason: We want these ranges to remain fixed when we copy the formula to other cells.

• Explanation: Absolute references (e.g., $A$2:$A$8 and $B$2:$B$8) ensure that when you paste the formula into any cell, Excel doesn't change the reference. This is crucial because we want to consistently sum sales amounts from the same product list (Column A) and sale amounts (Column B) for all products.

No Absolute Reference for Criteria:
• Reason: We want the criteria to adjust automatically when we copy the formula to below cells.

• Explanation: By not using absolute references for the criteria (e.g., D2), we allow Excel to dynamically update the criteria based on the relative position of the formula. So, when you copy the formula to cells below, Excel adjusts the criteria to D3, D4, and so on. This way, the formula automatically calculates the total sales amounts for each product specified in the criteria without manual changes.

In Summary:
• Absolute references for ranges: Keep the references fixed, ensuring consistency in data selection.

• No absolute reference for criteria: Allow Excel to adjust the criteria automatically, making the formula dynamic for different products.

This strategy makes the formula flexible. You can use it in various cells, and Excel will adapt the criteria while maintaining a consistent range and sum range for accurate calculations.

Chapter 6.10 - Averageif Function

The AVERAGEIF function in Excel is a helpful tool for calculating the average of a range of numbers based on a specified condition or criteria.
AVERAGEIF Syntax:
=AVERAGEIF(range, criteria, [average_range])
Here's what each part of the formula does:
range: This is the range of cells that you want Excel to evaluate based on the criteria. It's the "pool" of values that Excel will consider.

criteria: This is the condition or criteria you want to apply to the values in the range. Excel will only include values that meet this condition in the calculation.

[average_range] (optional): This is the range of cells containing the numbers you want to calculate the average for. If you leave this part out, Excel will use the same range as the "range" parameter for the calculation.

AVERAGEIF Example :
Let's understand Averageif Function with example :

Example 1: Calculating Average Sales for Each Product

Suppose we have a sales dataset as follows:
Figure 238

Task: Calculate the Average Sale Amount for Each Product

Steps:
Step 1: Set Up Criteria in Column D 1. In cell D2, type "Laptop." 2. In cell D3, type "Tablet." 3. In cell D4, type "Smartphone."

Figure 239
Step 2: Write AVERAGEIF Formula in Cell E2
• In cell E2, enter the following AVERAGEIF formula: =AVERAGEIF(A2:A8,D2,B2:B8)

• Select the range (A2:A8) within the formula.
• Press F4 to make it an absolute reference (e.g., $A$2:$A$8). Your formula now looks like

=AVERAGEIF($A$2:$A$8,D2,B2:B8)

• Select the Sale Amount range (B2:B8) within the formula.
• Press F4 to make it an absolute reference (e.g., $B$2:$B$8). Your formula now looks like

=AVERAGEIF($A$2:$A$8, D2, $B$2:$B$8).
Step 3: Press Enter
• After setting up the absolute references, press Enter. Now, cell E2 shows the average sale amount (550) for Laptop.

Step 4: Copy and Paste the Formula

• Copy the formula from cell E2.

• Paste it in cells E3 and E4 using various methods (e.g., doubleclicking the fill handle or using Ctrl+C and Ctrl+V).
Figure 240

Results:
• Cell E2 will display the Average Amount of Laptop (550).
• Cell E3 will display the Average Amount of Tablet (250).
• Cell E4 will display the Average Amount of Smartphone (426.67).

The AVERAGEIF function calculates average sale amounts based on criteria set for 'Laptop,' 'Tablet,' and 'Smartphone' in cells D2, D3, and D4. When copied to cells E3 and E4, the formula adjusts automatically for 'Tablet' and 'Smartphone,' enabling quick analysis without manual adjustments.

Using absolute references is crucial because it ensures that the formula always looks in the same range of items and rates, even when copied to different cells. This way, you get accurate and consistent results for different criteria without having to rewrite the formula for each condition.

Chapter 6.11 - Averageifs Function

The AVERAGEIFS function in Excel allows you to calculate the average of a range of numbers based on multiple criteria or conditions. It's a powerful tool for more complex data analysis.

AVERAGEIFS Syntax:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Here's what each part of the formula does:
- average_range : This is the range of cells containing the numbers you want to calculate the average for.
- criteria_range1 : This is the first range of cells where you want to apply the first condition.
- criteria1 : This is the first condition you want to apply to the data in `criteria_range1`.
- [criteria_range2, criteria2] (optional): You can add more pairs of criteria_range and criteria if you have multiple conditions.

Example :Let’s understand AVERAGEIFS Function with a example.

Suppose we have sales data as follows:
Figure 241

Task: Calculate the average sale amount for "Laptop" in the "North" region and for “Tablet” in the “South” region

Steps:
Step 1: Set Up Criteria in Columns E and F
1. In cell E2, type "Laptop".
2. In cell E3, type "Tablet".
3. In cell F2, type "North".

4. In cell F3, type "South".

Step 2: Write AVERAGEIFS Formula in Cell G2
• In cell G2, enter the following AVERAGEIFS formula: =AVERAGEIFS(C2:C9,A2:A9,E2,B2:B9,F2)

• Select the Sale Amount range (C2:C9) within the formula.
• Press F4 to make it an absolute reference (e.g., $C$2:$C$9).
Your formula now looks like

• Select the Product criteria range (A2:A9) within the formula.
• Press F4 to make it an absolute reference (e.g., $A$2:$A$9).

• Select the Zone criteria range (B2:B9) within the formula.
• Press F4 to make it an absolute reference (e.g., $B$2:$B$9).
Step 3: Press Enter
• After setting up the absolute references, press Enter. Now, cell G2 shows the average sale amount of laptops for the North zone. Result: Now, you can see the average sale amount (425) of laptops for the North zone in cell G2.

Copying Formula to G3:
• Copy the formula from cell G2.
• Paste it in cell G3.

• The formula adjusts automatically to calculate the average sale amount of tablets for the South zone.
Figure 242

This approach using the AVERAGEIFS function allows you to quickly calculate the average sale amount based on specific criteria, providing insights into product sales in different zones.

Chapter 6.12 - Countif Function

The COUNTIF function in Excel allows you to count the number of cells that meet a specific condition or criteria within a range. It's a handy tool for analyzing and summarizing data.

COUNTIF Formula Syntax: =COUNTIF(range, criteria)
Here's what each part of the formula does:
- range : This is the range of cells where you want to apply the condition and count the matching cells.
- criteria : This is the condition you want to apply to the cells in the range. Excel will count the cells that meet this condition.

Example :Let’s understand Countif Function with some example. Example: Using COUNTIF Function

Suppose we have a list of products in Column A and we want to count how many times the product "Laptop" appears in the list.

Steps:
Step 1: Set Up Criteria in Cell C2 1. In cell C2, type "Laptop."
Figure 243
• In cell D2, enter the following COUNTIF formula:
=COUNTIF(A2:A10,C2)

• Select the range (A2:A10) within the formula.
• Select the cell C2 “Laptop” after giving comma..
• Press Enter.

Result: Now, cell D2 shows the count of how many times the product "Laptop" appears in the list.
Figure 244
Explanation:

• The COUNTIF function counts the number of cells within the range A2:A10 that contain the value "Laptop," which is 4 in this case. This approach using the COUNTIF function is handy for quickly determining the frequency of a specific item or category in a dataset, providing valuable insights into the data.

Sales Data Table:
Suppose we have a list of products in Column A and we want to count how many times the product "Laptop" and "Tablet" appears in the list.
Figure 245

Steps:
Step 1: Set Up Criteria in Cells C2 and C3
1. In cell C2, type "Laptop."
2. In cell C3, type "Tablet."
• In cell D2, enter the following COUNTIF formula:
=COUNTIF(A2:A10,C2)
• Select the range (A2:A10) within the formula.

• Press the F4 key on your keyboard to freeze the range as an absolute reference.

• Select the cell C2 after giving comma.
• Press Enter.

Step 3: Copy and Paste the Formula to Cell D3
• Copy the formula from cell D2.
• Paste it in cell D3.

Figure 246
Results:
• Cell D2 shows the count of how many times the product "Laptop" appears in the list, and the result is 4.
• Cell D3 shows the count of how many times the product "Tablet" appears in the list, and the result is 3.
Explanation:

• The COUNTIF function counts the number of cells within the range A2:A10 that contain the value specified in cells C2 and C3. By freezing the range as an absolute reference, the formula can be easily copied and pasted to count occurrences for different products without manually adjusting the range.

Chapter 6.13 - Countifs Function

The COUNTIFS function in Excel allows you to count the number of cells that meet multiple conditions or criteria within a range. It's an advanced tool for analyzing and summarizing data.

COUNTIFS Syntax:
=COUNTIFS(range1, criteria1, [range2, criteria2], ...)
Here's what each part of the formula does:
- range1: This is the first range of cells where you want to apply the first condition and count the matching cells.
- criteria1: This is the first condition you want to apply to the cells in `range1`.
- [range2, criteria2] (optional): You can add more pairs of range and criteria if you have multiple conditions.

Example : Let’s understand Countifs Function with a Example Scenario:

Imagine you're managing sales data for a technology store. The sales data table includes information about products sold and the zones where these products were sold. You want to track how many times specific products were sold in different zones.

Task: You want to find out:
1. How many times the product "Laptop" was sold in the North zone.
2. How many times the product "Tablet" was sold in the South zone.

Figure 247

Steps:
Step 1: Set Up Criteria in Column D and E 1. In cell D2, type Laptop.
2. In cell D3, type Tablet.
3. In cell E2, type North.
4. In cell E3, type South.

Step 2: Write COUNTIFS Formula in Cell F2
• In cell F2, enter the following COUNTIFS formula:

=COUNTIFS(A2:A10,D2,B2:B10,E2)

• Select the range for Product (A2:A10) within the formula.

• Press the F4 key on your keyboard to freeze the range for Product as an absolute reference.

• Select the range for Zone (B2:B10) within the formula.
• Press the F4 key on your keyboard to freeze the range for Zone as an absolute reference.
Your formula now looks like
=COUNTIFS($A$2:$A$10,D2,$B$2:$B$10,E2)

• Press Enter.

Step 3: Copy and Paste the Formula to Cell F3
• Copy the formula from cell F2.
• Paste it in cell F3.

Results:
• Cell F2 shows the count of how many times the product "Laptop" appears in the North zone, and the result is 4.
• Cell F3 shows the count of how many times the product "Tablet" appears in the South zone, and the result is 2.
Figure 248

Outcome: You now have a quick and automated way to track product sales in different zones, allowing you to make informed decisions about inventory and marketing strategies.

Chapter 6.14 - IF Function

The IF function in Excel allows you to make decisions based on conditions. It helps you perform different calculations or display different values depending on whether a condition is met or not.

IF Syntax:
=IF(logical_test, value_if_true, value_if_false)
Here's what each part of the formula does:
- logical_test : This is a condition you want to check. It can be any logical expression that evaluates to TRUE or FALSE.
- value_if_true : This is the value or calculation you want to return if the `logical_test` evaluates to TRUE.
- value_if_false : This is the value or calculation you want to return if the `logical_test` evaluates to FALSE.

Example : Let’s understand IF Function with some examples.

Imagine you have a table with student scores, and you want to determine whether a student has passed or failed based on a passing threshold.

Here's how you can do it:

Step 1: Set Up Your Data Table
Let's create a table with student names and their test scores:
Figure 249
Step 2: Apply IF Formula
Now, let's say the passing score is 60, and you want to determine whether each student has passed or failed.
In cell C2 (where you want the result for Alice), enter the formula: =IF(B2>=60,"Pass","Fail")
Figure 250

- B2 is the score for Alice.
- >= 60 is the condition to check if the score is greater than or equal to 60.
- "Pass" is the value to return if the condition is TRUE.
- "Fail" is the value to return if the condition is FALSE.

Step 3: Press Enter
After entering the formula, press Enter. Excel will check Alice's score, and because it's greater than or equal to 60, it will display "Pass" in cell C2.
Step 4: Copy the Formula

To apply the same logic to other students, you can simply copy the formula in cell C2 and paste it in cells C3 to C6. Excel will adjust the references automatically.

Step 5: Review the Results
In this example, you'll see that Excel has determined whether each student has passed or failed based on their scores.

Example 2: Classifying Grades

Suppose you have a list of student scores, and you want to assign letter grades based on the following criteria:

- A: Score greater than or equal to 90.
- B: Score greater than or equal to 80 but less than 90.
- C: Score greater than or equal to 70 but less than 80.
- D: Score greater than or equal to 60 but less than 70.
- F: Score below 60.

In cell C2 (where you want the letter grade for Alice), enter the formula: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))
Figure 253
This nested IF function checks the score against each criteria and assigns the appropriate letter grade.
Copy the Formula
Copy the formula in cell C2 and paste it in cells C3 to C6 to calculate grades for other students.
Figure 254

The IF function in Excel is incredibly versatile and can be used to make decisions and perform calculations based on various conditions, making it an essential tool for data analysis and automation.

Chapter 6.15 - IFS Function

The IFS function in Excel is a versatile way to evaluate multiple conditions and return a value based on the first true condition. It simplifies complex nested IF functions.

IFS Syntax:
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
Here's what each part of the formula does:
- `logical_test1`: This is the first condition you want to check. It should be a logical expression that evaluates to TRUE or FALSE.
- `value_if_true1`: This is the value or calculation you want to return if `logical_test1` is TRUE.
- `[logical_test2, value_if_true2]`: You can add more pairs of conditions and values if you have multiple conditions to evaluate.

Example :

Imagine you have a table with student scores, and you want to assign letter grades based on the following criteria:

- A: Score greater than or equal to 90.
- B: Score greater than or equal to 80.
- C: Score greater than or equal to 70.
- D: Score greater than or equal to 60.
- F: Score below 60.

Here's how you can do it using the IFS function:
Step 1: Set Up Your Data Table
Let's create a table with student names and their test scores:

Figure 255
Step 2: Apply IFS Formula
Now, you can use the IFS function to assign letter grades. In cell C2 (where you want the letter grade for Alice), enter the following formula: =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")
- B2 >= 90 checks if the score is greater than or equal to 90. If true, it returns "A."
- B2 >= 80 checks if the score is greater than or equal to 80. If true, it returns "B."
- B2 >= 70 checks if the score is greater than or equal to 70. If true, it returns "C."
- B2 >= 60 checks if the score is greater than or equal to 60. If true, it returns "D."
- TRUE serves as a catch-all condition and returns "F" if none of the previous conditions are met.
Figure 256
Step 3: Press Enter
After entering the formula, press Enter. Excel will evaluate the conditions and assign the appropriate letter grade for Alice.
Step 4: Copy the Formula
To apply the same logic to other students, copy the formula in cell C2 and paste it in cells C3 to C6. Excel will adjust the references automatically.
Step 5: Review the Results
In this example, you'll see that Excel has assigned letter grades to each student based on their scores using the IFS function.
Figure 257

Chapter 6.16 - IF function with AND, OR, NOT Functions

Using the IF function with the AND, OR, and NOT functions allows you to create more complex conditions in Excel. Let's explore how to use these functions.

1. Using IF with AND Function:

The AND function in Excel checks if all specified conditions are true. You can use it within an IF function to make decisions based on multiple conditions.

Syntax for IF with AND:
=IF(AND(condition1, condition2, ...), value_if_true, value_if_false)
Here's what each part of the formula does:
- condition1, condition2, ...: These are the conditions you want to check. They can be logical expressions.
- value_if_true: The value or calculation to return if all conditions are true.
- value_if_false: The value or calculation to return if any of the conditions are false.

Example:

Suppose you have a list of exam scores for students in different subjects, and you want to categorize their performance. If a student scores above 70 in both Math and English, you label them as "Pass"; otherwise, they are labeled as "Fail."

Student Scores Table:
Figure 258

Task : You want to create a formula that checks if a student passed in both Math and English.

Steps:
Step 1: Write the IF with AND Formula in Cell D2
• In cell D2, enter the following formula:
=IF(AND(B2>70,C2>70),"Pass","Fail")

• Here, B2>70 checks if the Math score is above 70.
• C2>70 checks if the English score is above 70.
• The AND function ensures both conditions are met.

• If both conditions are true, the student "Passes"; otherwise, they "Fail."
Figure 259

Step 2: Press Enter
• After entering the formula, press Enter.
Step 3: Copy and Paste the Formula
• Copy the formula from cell D2.
• Paste it in cells D3 to D5 to apply the logic to other students.
Results:

• Cell D2 and D5 show "Pass" for John and Carol since they passed in both subjects.
• Cells D3 and D4 show "Fail" for Alice and Bob since they did not pass in both subjects.
Figure 260

Outcome: You've now efficiently categorized students based on their scores in Math and English, making it easy to identify those who meet the passing criteria.

2. Using IF with OR Function:

The OR function in Excel checks if at least one of the specified conditions is true. You can use it within an IF function to make decisions based on multiple conditions.

Syntax for IF with OR:
=IF(OR(condition1, condition2, ...), value_if_true, value_if_false)
Here's what each part of the formula does:
- condition1, condition2, ...: These are the conditions you want to check. They can be logical expressions.
- value_if_true: The value or calculation to return if at least one condition is true.
- value_if_false: The value or calculation to return if none of the conditions are true.

Example :Using IF with OR Function

Suppose you have a list of exam scores for students in different subjects, and you want to categorize their performance. If a student scores above 70 in either Math or English, you label them as "Pass"; otherwise, they are labeled as "Fail".

Scores Table :

Figure 261

Task: You want to create a formula that checks if a student passed in either Math or English.

Steps:
Step 1: Write the IF with OR Formula in Cell D2
• In cell D2, enter the following formula:
=IF(OR(B2>70,C2>70),"Pass","Fail")

• Here, B2>70 checks if the Math score is above 70.
• C2>70 checks if the English score is above 70.

• The OR function ensures that if either condition is true, the student "Passes."
Figure 262
Step 2: Press Enter
• After entering the formula, press Enter.

Step 3: Copy and Paste the Formula
• Copy the formula from cell D2.
• Paste it in cells D3 to D5 to apply the logic to other students.

Figure 263

Results:
• Cell D2 shows "Pass" for John since he passed in Math.

• Cells D3 and D4 show "Pass" for Alice and Bob since they passed in English and Math, respectively.
• Cell D5 shows "Pass" for Carol since she passed in both subjects.

Outcome: You've now efficiently categorized students based on their scores in either Math or English, making it easy to identify those who meet the passing criteria.

3. Using IF with NOT Function:

The NOT function in Excel negates a logical value. You can use it within an IF function to reverse a condition.
Syntax for IF with NOT:
=IF(NOT(condition), value_if_true, value_if_false)

Here's what each part of the formula does:
- condition : The condition you want to reverse (a logical expression).
- value_if_true : The value or calculation to return if the condition is false.
- value_if_false : The value or calculation to return if the condition is true.

Example:

Suppose you have a list of exam scores for students, and you want to categorize their performance. If a student does not score above 60 in Math, you label them as "Fail"; otherwise, they are labeled as "Pass."

Student Scores Table:
Figure 264

Task : You want to create a formula that checks if a student failed in Math.

Steps:
Step 1: Write the IF with NOT Formula in Cell C2
• In cell C2, enter the following formula:
=IF(NOT(B2>60),"Fail","Pass")
• Here, B2>60 checks if the Math score is above 60.
• The NOT function negates the condition, so if the Math score is not above 60, the student "Fails."
Figure 265
Step 2: Press Enter
• After entering the formula, press Enter.

Step 3: Copy and Paste the Formula
• Copy the formula from cell C2.
• Paste it in cells C3 to C5 to apply the logic to other students.

Results:
• Cell C2 shows "Fail" for John since his Math score is below 60.

• Cells C3 and C4 show "Pass" for Alice and Bob since their Math scores are above 60.
• Cell C5 shows "Fail" for Carol since her Math score is below 60.
Figure 266
Outcome: You've now efficiently categorized students based on their Math scores, specifically identifying those who did not meet the passing criteria.

Using the IF function with AND, OR, and NOT functions allows you to create more flexible and precise conditions for decision-making in Excel. These combinations help you handle complex scenarios effectively.

Chapter 6.17 - Index Function

The INDEX function in Excel is designed to retrieve a value from a specific position within a range of cells. It is a versatile tool for dynamically accessing data.

Formula Syntax:
=INDEX(range, row_number, [column_number])

Here's what each part of the formula does:
• range: The array or range of cells from which to retrieve the value.

• row_number: The row number within the range to choose the value.
• column_number (optional): The column number within the range (if the range is two-dimensional).
Example:
Suppose you have a table of product prices:
Figure 267

Task : use the INDEX function to retrieve the price of "Tablet" (in cell D2). Write the formula in D2:
=INDEX(B2:B5,2)

Figure 268

Explanation:
• B2:B5: The range of prices.
• 2: The row number (position of "Tablet" in the list (selected range).

When you press Enter, the formula returns the value $400, which is the price of "Tablet."
Figure 269
Benefits of INDEX Function:

1. Dynamic Retrieval: The INDEX function allows dynamic access to data based on specified criteria, making it adaptable for various scenarios.

2. Array Flexibility: It can handle both one-dimensional and twodimensional arrays, providing versatility in data structures.

3. Combination with Other Functions: The INDEX function often works in conjunction with functions like MATCH for powerful and flexible data lookups.

In summary, the INDEX function is a valuable tool for retrieving specific values from a range, offering flexibility and efficiency in Excel data manipulation.

Chapter 6.18 - Match Function

The MATCH function in Microsoft Excel allows you to find the relative position of a specified value within a range or array. It's often used in combination with other functions like INDEX and VLOOKUP to look up data. Let’s understand Match function:

Match Formula Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
Here's what each part of the formula does:

• lookup_value: This is the value you want to find within the lookup_array.
• lookup_array: This is the range or array where you want to search for the lookup_value.
• match_type (optional): The type of match - 1 for less than, 0 for an exact match, -1 for greater than.

Example:

Suppose you have a list of products in column A and you want to find the position of "Tablet" in the list.
Figure 270
You can use the MATCH function as follows: =MATCH("Tablet",A1:A4,0)
Figure 271

Explanation:
• "Tablet": The value to search for.

• A1:A4: The range where Excel
should look for the value.
• 0: The match_type specifies an exact
match.
When you press Enter, the formula returns 2, indicating that "Tablet" is in the second position in the list.
Benefits of MATCH Function:
1. Positional Awareness: MATCH provides the relative position of an item in a list, aiding in data analysis and referencing.
2. Versatility: It can be used with other functions like INDEX for powerful data retrieval.
3. Dynamic Matching: The optional match_type parameter allows for flexible matching criteria.

In summary, the MATCH function is a valuable tool for determining the position of a value within a range, adding flexibility and efficiency to Excel data analysis.

Chapter 6.19 - Using Index and Match Together

Combining INDEX and MATCH functions is a powerful technique in Excel for looking up and retrieving data. While VLOOKUP requires the lookup value to be in the leftmost column, INDEX and MATCH offer more flexibility.

Formula Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_array, 0))
Here's what each part of the formula does:
• return_range: The range of cells containing the data you want to retrieve.
• lookup_value: The value to search for within the lookup_array.
• lookup_array: The range of cells containing possible matches.

Example 1

Suppose you have a table of sales data where column A contains product names, column B contains sales amounts, and you want to find the sales amount for a specific product (e.g., "Tablet").

Figure 272
You can use the following formula:
=INDEX(B2:B5,MATCH("Tablet",A2:A5,0))
Figure 273
Explanation:
• INDEX(B2:B5, ...): Retrieves the corresponding sales amount from column B.
• MATCH("Tablet", A2:A5, 0): Finds the position of "Tablet" in the list.
When you press Enter, the formula returns 300, the sales amount for the product "Tablet."
Benefits of Using INDEX and MATCH Together:
1. Flexibility: Unlike VLOOKUP, INDEX and MATCH aren't restricted to having the lookup column on the left.
2. Dynamic Range: The combination allows for dynamic lookups and is not limited by the position of columns.
3. Two-Dimensional Lookup: You can perform lookups based on both rows and columns.

Example 2 : Dynamic Employee Salary Lookup

Suppose you have an employee database with names in column A, job titles in column B, and corresponding salaries in column C.
Figure 274

Now, let's use the following setup:
In Column E:
• Cell E2: "Bob" (Employee name)
• Cell E3: "Emily" (Employee name)

In Column F:
• Cell F2: "Manager" (Job title for Bob)
• Cell F3: "Analyst" (Job title for Emily)

Column G :
In Column G, we'll use a formula to dynamically calculate salaries based on employee names and job titles.
Cell G2 (Bob's Salary):
• In G2, enter the formula:
=INDEX(C2:C10, MATCH(E2&F2, A2:A10&B2:B10, 0))

• This formula combines Bob's name (in cell E2) with his job title (in cell F2) and looks for a match in the concatenated list of names and job titles.

• The INDEX function retrieves Bob's salary corresponding to the matched position.
Important Note:

• Ensure to convert the range and arrays into absolute references to maintain consistency when copying the formula to other cells. After converting, the formula will look like:

=INDEX($C$2:$C$10,MATCH(E2&F2,$A$2:$A$10&$B$2:$B$10,0))
Cell G3 (Emily's Salary):

• Copy the formula from G2 and paste it into G3.
• The formula adapts to calculate Emily's salary by using her name (in cell E3) and job title (in cell F3).

Figure 275

These formulas dynamically fetch salaries based on the specified employee names and job titles. Copying the formula allows you to easily calculate salaries for different employees by changing the names in cells E2 and E3.

Explanation:
• INDEX Function:
• Retrieves the corresponding salary based on the matched position.
• MATCH Function:
• Concatenates the employee name and job title.
• Finds the position of the combined criteria in the list.

When you copy the formula from G2 to G3, it adapts to calculate Emily's salary dynamically based on the specified job title, making it easy to use the same formula for different employees with different job titles.

Chapter 6.20 TextJoin Function

The TEXTJOIN function in Excel is a powerful way to combine text strings from a range of cells, with the option to specify a delimiter (such as a comma or space) between the text values.

Function Name: TEXTJOIN
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Arguments:

• delimiter: The separator you want between the text values.

• ignore_empty: A logical value (TRUE or FALSE) that specifies whether to ignore empty cells. If set to TRUE, it excludes empty cells from the result.

• text1, [text2], ...: The text values you want to concatenate. Explanation:

The TEXTJOIN function allows you to easily combine text values from different cells with a specified delimiter in between. It's particularly useful when you want to merge data from multiple cells into a single cell.

Here's how it works:

1. delimiter: You provide the delimiter as the first argument. For example, if you want to separate the text with a comma and space, you would enter ", ".

2. ignore_empty: You specify whether to ignore empty cells. If you set this to TRUE, empty cells are skipped in the concatenation.

3. text1, [text2], ...: You list the text values you want to combine. These can be cell references, text enclosed in double quotes, or other formulas that result in text.

Example:
Suppose you have the following data in Excel:
Figure 276

To combine the text values from columns A to D into a single cell in column E with a comma and space separator, you can use the TEXTJOIN function like this:

Steps:
1. Click on the first cell in column E (E2, in this case) where you want to display the combined info. This is where the result will be shown. 2. Enter the following formula:
=TEXTJOIN(", ",TRUE,A2,B2,C2,D2)
• ", " is the delimiter, specifying a comma and space between values.
• TRUE tells Excel to ignore empty cells.

• A2, B2, C2, and D2 are references to the cells in columns A to D for the first row.
3. Press Enter. You'll now see the combined information in cell E2:

Figure 277

4. To apply this formula to the entire column E, you can use the fill handle (a small square at the lower-right corner of the selected cell). Click and drag it down to copy the formula for other rows.

Result:

After dragging the fill handle down to apply the formula to all rows in column E, you'll have the combined information for each row with the desired format:

Figure 278

By using the TEXTJOIN function, you can easily concatenate text values from different columns with the specified delimiter, making your data more readable and organized.

Part VII: Advanced Data Analysis

Chapter 7.1 - Pivot Table for Summary & Reports

Benefits of Using Pivot Table:
1. Data Summarization: Pivot tables allow you to summarize and analyze large datasets quickly and easily.
2. Dynamic Analysis: You can rearrange and pivot your data to view it from various angles for deeper insights.
3. User-Friendly: They don't require complex formulas or functions, making data analysis accessible to all Excel users.
4. Aggregation: You can aggregate data by sum, count, average, etc., without manual calculations.
5. Customization: Pivot tables enable you to create custom reports and charts based on your data.

Example : Let’s understand Pivot Table with some examples.

Example 1 : Analyzing Sales Data
Suppose we have sales data in the following format and we want to analyze monthly sales data.
Figure 279
Steps for using Pivot Table:
1. Select Your Data:

• Click anywhere in your dataset to select it. 2. Insert Pivot Table:
• Go to the "Insert" tab in the Excel ribbon.
• Click on "PivotTable." A dialog box will appear.

Figure 280

3. Choose Data Source:
• Ensure the Table/Range is correctly selected.
• Choose to place the Pivot Table in a new worksheet.

4. Design Your Pivot Table:
• In the new worksheet, you'll see a blank grid.
Figure 281

• Drag the "Date" field to the "Rows" area.
• Drag the "Product" field to the "Columns" area.

• Drag the "Sales" field to the "Values" area. It will default to sum.

Click the checkbox next to
the field to add or remove
it from the Pivot Table.

Figure 282

After you've dragged and dropped your fields into areas like Columns, Rows, and Values, take a look at your Pivot Table — it's like arranging pieces of a puzzle. The data now shows up in a way that's easier to understand and analyze.

Figure 283
5. Customize the Pivot Table:
• You can further customize your Pivot Table by dragging fields to different areas.
• For instance, you could drag "Product" to "Rows" to see sales per product over time.
6. Filter and Sort:
• You can filter and sort data within the Pivot Table to focus on specific information.
Result:
You'll now have a dynamic Pivot Table summarizing your sales data, allowing you to quickly analyze trends and patterns.

Pivot Tables are powerful tools for summarizing and analyzing large datasets, and with these steps, you can create one to suit your specific needs.

Example 2 : Scenario: Monthly Product-wise Sales Analysis

Suppose you have a excel sheet containing the company's sales information, structured as follows:
Figure 284

Task:Now, you want to analyze your company's sales data on a monthly

basis to understand the monthly performance of each product. Here's how you can achieve this using a PivotTable:

Follow these steps for Monthly Product-wise Sales Analysis

Steps:
1. Select Your Data:

• Click anywhere in your dataset to select it. 2. Insert a PivotTable:
• Go to the "Insert" tab in Excel.
• Click on "PivotTable."

• Ensure the Table/Range is correctly selected, and choose where you want to place the PivotTable (e.g., a new worksheet).

3. PivotTable Fields:
• You'll see a blank PivotTable and a Field List.
Figure 286
• Drag "Item Name" field to the Columns area.
• Drag "Amount" field to the Values area. Ensure it's set to summarize as "Sum."
• Drag "Date" field to the Rows area.(see figure 287)

4. Group Dates by Months:
• Right-click on any date in the PivotTable.
• Choose "Group."
• Check "Month" and click "OK."

Note - If you want to see Quarterly sales, check the option "Quarters" to group your dates by quarters. If you prefer to look at Yearly sales, group your dates by years.

5. Format if Needed:
• Format the sales amount to display as currency if necessary.
6. Analyze:

• Your PivotTable now provides a summarized view of product-wise sales amounts for each month.
After following these steps, you see a PivotTable like this:

Figure 290

Note If your data includes dates spanning multiple months, Excel will automatically display it monthly. To view day-wise, quarterly, or yearly data, right-click on the date/month or quarter in the pivot table, select "Group," and then choose the desired grouping option (Days /Months /Quarters or Years) based on your preference.

Figure 291

Example 3 : Analyzing Product Sales Data Scenario: Suppose you have a sales dataset with columns like Date of Sale

in Column A, Product Name in Column B, Sales Amount in Column C,. Your goal is to analyze how many times each product has been sold using a Pivot Table.

Steps:

Step 1: Prepare Your Data: Ensure your data is organized with clear headers, including columns for Product Name, Sales Amount, and Date of Sale.

Figure 292

Step 2: Insert a Pivot Table:
• Click anywhere in your dataset to select it.
• Go to the "Insert" tab and choose "PivotTable."

• Confirm the range and choose where you want the PivotTable report (e.g., a new worksheet).
• Click "OK."
Step 3: Configure the Pivot Table:
• Drag the "Item Name" field to the "Rows" area. (see figure 293)
• Drag the "Sales Amount" field to the "Values" area. Ensure it is set to count the number of sales.. (see figure 293)
• Your PivotTable will show a count of each product's sales. Figure 293

Step 4: Setting Sales Amount in Count Format:

If you want to see how many times each product has been sold using the sales amount field in your Pivot Table, follow these simple steps: 1. Click the dropdown arrow next to the 'Sales Amount' field in the pivot table.
Figure 294
2. Select 'Value Field Settings'.
3. In the 'Value Field Settings' window, choose 'Count' from the 'Summarize by' dropdown menu.
4. Click 'OK'. Your pivot table will now display the number of times each product was sold."
Step 4: Analyze the Results:
• Your PivotTable will display each product and the count of sales for each.
Result: You now have a clear analysis of how many times each product has been sold, making it easy to identify top-performing products.
Figure 295
This approach allows you to quickly summarize and analyze sales data using a Pivot Table, providing valuable insights into your product sales.

Step 6 : Renaming Pivot Table Data Heading:

If you want to give a custom name to your Pivot Table data heading, follow these simple steps:
1. Double-click on the heading of the data field you want to rename in your PivotTable.
Figure 296
Double click on the Heading
2. A dialog box for "Value Field Settings" will appear.
3. In this dialog box, you can mention a custom name in the "Custom Name" field.
4. Click "OK" to apply the custom name.
By doing this, you can personalize your Pivot Table and make it more userfriendly, reflecting the specific information you're analyzing.

Step 7 : Modifying Pivot Table Fields Directly:

In a Pivot Table, modifying some fields is as easy as changing a cell value. No need for complex steps!
For Example Changing Row Labels or Column Labels:
• Simply click directly on the row label or column label cell you want to modify in your Pivot Table.
• Edit it right there; there's no need to double-click or go through additional menus.

This direct modification feature makes it quick and straightforward to customize your Pivot Table according to your preferences. Just click, edit, and you're done!

Before
After Changing the Heading

Bonus Tip 1 :

Organize Two Fields : You can organize two fields in the Rows or Columns area by dragging and dropping. Adjust their order easily by clicking the dropdown arrow button or dragging them.

Figure 297
Remove a Field : To remove a field, simply click the dropdown arrow button and choose 'Remove Field.

Bonus Tips 2 : Pivot Table Design Tab Options:

The Pivot Table Design tab in Excel offers handy options to enhance the look and feel of your pivot table.
• Report Layout:

• Choose between Compact, Outline, and Tabular layouts to organize your data the way you prefer.
• Grand Totals and Subtotals:
• Decide where you want to display Grand Totals and Subtotals, whether at the top, bottom, or both.
• Blank Rows:
• Control the appearance of blank rows, adding clarity to your table.
• Themes:
• Apply various visual themes to your pivot table, ensuring it aligns with your document's aesthetics.

I recommend experimenting with these options in your pivot table report. Just click on them and observe how they impact your data. Most of these options can make your pivot table report more attractive, so feel free to try them out all at once!

Bonus Tips 3 :

Show / Hide Pivot Table Field List in Excel:
• To show the PivotTable Field List, right-click anywhere inside your pivot table report, and choose "Show Field List."

• To hide the PivotTable Field List, right-click inside your pivot table report again, and select "Hide Field List."

• Alternatively, you can use the PivotTable Analyze or Options tab in the Excel ribbon to toggle the visibility of the Field List.

Refresh Pivot Table

Refreshing a Pivot Table in Excel is crucial to reflect any changes made to the original data. Let's go through the different ways to refresh a Pivot Table:

1. Right-Click and Refresh:
- Right-click on any cell within the Pivot Table.
- From the context menu, select "Refresh."

2. Using the Ribbon:
- Click anywhere inside the Pivot Table.

- Go to the " Analyze" or "Options" tab on the Excel ribbon (this tab may vary depending on your Excel version).

- In the "Data" group, click on "Refresh."

3. Keyboard Shortcut:
- Select any cell in the Pivot Table.
- Press the "Alt" key on your keyboard.
- While holding "Alt," press the " F5" key.

4. Automatic Refresh Options:
- Pivot Tables can be set to automatically refresh when you open the Excel file or at specific time intervals.
- To set automatic refreshing, right-click inside the Pivot Table and select "PivotTable Options."

- In the "PivotTable Options" dialog box, go to the " Data" tab.
- Under "PivotTable Data," choose Refresh data when opening the file.

Now, when you close and reopen the Excel file, your data will update automatically.
5. Refresh All Data:
- If you have multiple Pivot Tables connected to the same data source, you can refresh all of them simultaneously.

- Click anywhere inside one of the Pivot Tables.
- Go to the "PivotTable Analyze" or "Options" tab.
- In the "Data" group, click on "Refresh Dropdown."
- Now click on "Refresh All"

Why Pivot Table Refresh is Necessary:
- Keep in mind that Pivot Tables do not automatically update when you modify the source data.

- If you make changes to your original data, such as adding, deleting, or editing records, your Pivot Table will not reflect these changes until you manually refresh it.

- Use one of the methods mentioned above to refresh the Pivot Table after altering the source data.

Chapter 7.2 - Slicer Function

The Slicer function in Excel is a handy tool that allows you to filter and control data in an Excel Table in a visually appealing way.
Let’s understand Slicer function with a example :
Scenario: Imagine you have a table containing sales data with columns such as Product, Zone, and Sales Amount.
Step 1: Convert Your Data into an Excel Table

Before you can use the Slicer function, you need to convert your data into an Excel Table. This step makes it easier to work with your data and enables the use of slicers.

1. Click anywhere in your data that you want to convert into a table. 2. Press Ctrl + T on your keyboard, or go to the "Insert" tab in the Excel ribbon and click on "Table."

Step 2: Add Slicer to the Table
• Click anywhere within your Excel Table.
• Navigate to the " Table Design" tab.
• Click on "Insert Slicer."

Figure 299
• Choose the columns you want to filter (e.g., Product and Zone) and click "OK."

Step 3: Use the Slicer
• A separate window with filter options (Slicer) will appear.

• Click on the values you want to display in your table. This visually filters your data.
Figure 301

Important Suppose you're interested in checking sales data for Laptops specifically in the North zone. To do this, simply click on "Laptop" in the Product slicer, and then click on "North" in the Zone slicer. This will instantly filter and show you the relevant data for Laptops in the North zone. It's like telling Excel to only display the information you're interested in with just a couple of clicks!

Step 4: Customize the Slicer
• Resize, move, or customize the appearance of the Slicer by clicking and dragging its edges or right-clicking on it.
Step 5: Clear Filters
• To clear filters, click on the "Clear Filter"
button in the Slicer.

Result: Now, you have a dynamic and visually appealing way to filter your Excel Table using the Slicer. It makes data exploration and presentation more interactive and user-friendly.

The Slicer function is a fantastic feature for enhancing the visual control and analysis of your Excel Table data.

Note:The slicer function works similarly in an Excel table and a pivot

table. Once you've mastered using slicers in one context, you can seamlessly apply the same techniques in the other.

Additional Setting of Slicers Using Slicer Tab

The Slicer Tab Columns, Height, and Width
settings allow you to customize the appearance of
your slicers in Excel.

1. Columns:

• This setting lets you choose how many columns you want your slicers to display. If you have a lot of items in your slicer and want to arrange them in multiple columns to save space, you can adjust this setting accordingly.

2. Height:

• The Height setting determines the height of your slicer. You can increase or decrease it based on your preference and the space available in your Excel workbook.

3. Width:

• Similar to Height, the Width setting controls the width of your slicer. Adjust this setting to make your slicer wider or narrower, depending on your layout requirements.

These settings provide flexibility in organizing and displaying slicers to fit the design and functionality you desire in your Excel workbook.

Chapter 7.3 - Pivot Chart

Creating a PivotChart in Excel is a great way to visualize and analyze data from a PivotTable. Here's how to do it:
Let’s understand Pivot Chart with a example.

Example: Creating a PivotChart with PivotTable Data

Scenario: Imagine you have sales data with dates, product names, and sales amounts. You want to analyze this data visually using a PivotChart.
Step 1: Prepare Your Data
Assume you have the following sales data:
Figure 303

Step 2: Create a PivotTable (if not already done)
1. Highlight your data range.
2. Go to the "Insert" tab on the Excel ribbon.

3. Click "PivotTable" and choose the location for your PivotTable (e.g., a new worksheet).

4. Drag the "Date" field to the Rows area, the "Product" field to the Columns area, and the "Sales Amount" field to the Values area (as Sum).

Note We have group the date in Months.

Step 3: Create a PivotChart
1. Click anywhere inside the PivotTable.
2. Go to the "Insert" tab.

Figure 304

3. Click "PivotChart" and choose the chart type you prefer (e.g., bar chart, column chart, pie chart, etc.). Choose the one that best suits your data.
4. A new chart will appear linked to your PivotTable.

Filter Pivot Table and Pivot
Chart Using these Buttons
Figure 305

Step 4: Customize Your PivotChart
1. Click on elements of the PivotChart to select them.
2. You can customize the chart title, axis labels, and legend directly.

3. To change the chart type or add more details, right-click on the chart, and choose the appropriate options.
Note - Check out the 'Creating Charts' Chapter 4.5 to learn how to customize your Pivot Chart and make it look exactly the way you want.

Step 5: Analyze Your Data with the PivotChart
• Your PivotChart will now appear alongside your PivotTable.

• You can further customize your PivotChart by right-clicking on chart elements and selecting options like "Add Data Labels," "Change Chart Type," "Format Chart Area," and more.

• As you make changes in your PivotTable (e.g., filtering data, updating values), the PivotChart will automatically update, helping you visually analyze your data.

Step 6: Save and Share Your PivotChart
• Save your Excel workbook to keep your PivotChart and data intact.

• You can also copy and paste the PivotChart into other documents or presentations.

By creating a PivotChart, you can quickly gain insights and analyze data in a visual format. PivotCharts are dynamic and update automatically as your data changes, making them a powerful tool for data analysis in Excel.

Chapter 7.4 – Timeline Feature

The Timeline feature in Excel is primarily used in pivot tables to filter data based on date fields, creating an interactive visual representation of data over time. Let’s understand Timeline with an example:

Example :

Scenario: Imagine you have a dataset with sales data, including dates, product names, and sales amounts. You want to analyze sales trends using a Pivot Table and utilize the Timeline feature for more dynamic filtering.

Figure 306

Steps:
1. Create a Pivot Table:
• Highlight your data.
• Navigate to the "Insert" tab and select "PivotTable."
• Choose the destination for your Pivot Table and click "OK."

2. Add Fields to Pivot Table Areas:
• Drag the Date field to the Rows area.
• Place the Product field in the Columns area.

• Drag the Sales Amount field to the Values area, ensuring it shows the sum of sales.

3. Insert a Timeline:
• Click anywhere in the Pivot Table.
• Go to the "PivotTable Analyze" tab.
• Click on "Insert Timeline."
• Choose the Date field and click "OK."

4. Filter Data with Timeline:
• The timeline appears. Adjust its duration by dragging the edges.
• Click on specific time intervals to filter your Pivot Table by date.

Figure 307

See in the screenshot below: by choosing June in the timeline, the Pivot Table adjusts to display data specifically for the month of June.

Figure 308
Figure 309
Note – You can adjust its duration by dragging the edges

The timeline feature allows you to interactively filter data based on specific date ranges, making it easier to analyze time-based information in your pivot tables.

Part VIII: Workbook Security & Sheet Management

Chapter 8.1 - Protect and Unprotect Excel Workbook

Protecting an Excel workbook with a password helps prevent other users from performing actions such as:
1. Viewing Hidden Worksheets: Users won't be able to access hidden sheets.
2. Adding, Moving, or Deleting Worksheets: Unauthorized changes to the structure are blocked.

3. Hiding Worksheets: Users can't hide worksheets to obscure data.
4. Renaming Worksheets: The original sheet names remain intact.

In simple terms, it's like locking the blueprint of your workbook, ensuring others can't rearrange or access certain parts of it without your permission.
Here's a step-by-step guide :

Protecting an Excel Workbook:
1. Open Excel: Launch Microsoft Excel and open the
workbook you want to protect.

2. Select the "Review" Tab: Go to the "Review" tab
on the Excel ribbon.

3. Click on "Protect Workbook": In the "Protect"
group on the "Review" tab, click on "Protect Workbook."
4. Set a Password (Optional): You can choose to set a password for the workbook. This step is optional but recommended for added security.

5. Re-enter the Password: Confirm the password by entering it again in the "Reenter password to open" field.
6. Click "OK": Click the "OK" button to apply the protection. If you set a password, you will need it to open the workbook in the future.
7. Save the Workbook: Don't forget to save the workbook after applying protection to ensure that the changes are saved.
Unprotecting an Excel Workbook:
1. Open Excel: Launch Microsoft Excel and open the protected workbook.
2. Select the "Review" Tab: Go to the "Review" tab on the Excel ribbon.
3. Click on "Protect Workbook": In the "Changes" group on the "Review" tab, click on "Protect Workbook."

4. Enter the Password (if applicable): If you set a password while protecting the workbook, you will be prompted to enter it. If not, you can skip this step.

5. Click "OK": Click the "OK" button to remove the protection from the workbook.
6. Save the Workbook: After unprotecting the workbook, save it to retain the changes.

That's it! You've successfully protected and unprotected an Excel workbook. Remember to keep your passwords safe, especially if you use them to protect sensitive information in your workbooks.

Chapter 8.2 - Rename, Delete, Hide, Protect A Sheet

Here's a step-by-step guide on how to rename, delete, move, color, hide, and protect a sheet in Microsoft Excel:
Main Step :

To rename, delete, move, color, hide, or protect a sheet, right-click on the sheet tab where you want to perform these actions. Choose your desired option, such as rename, delete, move, color, hide, or protect.

Right Click on Sheet Tab
Figure 310
1. Renaming a Sheet:
- Right-click on the sheet tab you want to rename at the bottom of the Excel window.
- Select "Rename" from the context menu.
- Type in the new name and press Enter.
2. Deleting a Sheet:

- Right-click on the sheet tab you want to delete.
- Select "Delete" from the context menu.
- Confirm the deletion if prompted.

3. Moving a Sheet:

- Click and hold the sheet tab you want to move.
- Drag it to the desired position among the sheet tabs.
- Release the mouse button to move the sheet.

4. Coloring a Sheet:

- Right-click on the sheet tab you want to color.
- Select "Tab Color" from the context menu.
- Choose a color from the color palette.

5. Hiding a Sheet:
- Right-click on the sheet tab you want to hide.
- Select "Hide" from the context menu.
6. Unhiding a Sheet:

- Right-click on any visible sheet tab.
- Select "Unhide" from the context menu.

- In the "Unhide" dialog box, select the sheet you want to unhide and click "OK."
7. Protecting a Sheet:

To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password.

- Right-click on the sheet tab you want to protect.
- Select "Protect Sheet" from the context menu.

- Set a password if desired and configure protection options.
- Click "OK" to apply protection.
8. Unprotecting a Sheet:
1. Go to "Review" Tab: Click on the "Review" tab in the Excel ribbon.

2. Click "Unprotect Sheet": In the "Changes" group, click on "Unprotect Sheet." If a password was set, you will need to enter it.

Remember to save your workbook after making any changes to sheets, and keep passwords secure if you use sheet protection to prevent unauthorized access or changes.

Chapter 8.3 - Add & Remove Password From XL File

To keep your Excel data private, lock your Excel file with a password.

Adding a Password to an Excel File:

1. Open Your Excel File: Launch Microsoft Excel and open the workbook you want to password-protect.
2. Click on "File": In the top left corner, click the "File" tab.
3. Select "Info" on the Left: From the options on the left, click "Info."
4. Click on "Protect Workbook": In the "Info" section, you'll see a "Protect Workbook" option. Click on it.
Figure 311
5. Choose "Encrypt with Password": A dialog box will appear. Choose "Encrypt with Password."

6. Set Your Password: Enter the password you want to use. Make sure it's something you'll remember, as losing it can result in permanent data loss. We've chosen the password 123456 to unlock this Excel file, for instance.
7. Re-Enter Your Password: To confirm, enter the password again.

8. Save Your File: After setting the password, save your workbook. The password protection is now active.

Now, when you try to open the protected Excel file, Excel will ask you to enter a password to unlock and access the file. Simply type 123456 , as that's the password we set for this Excel file.

Removing a Password from an Excel File:

1. Open Your Excel File: Start by opening the password-protected Excel file.
2. Access "File" and "Info": Click on "File" and then "Info" from the menu on the left.

3. Choose "Protect Workbook" and "Encrypt with Password": Navigate to "Protect Workbook" and select "Encrypt with Password."

Figure 312

4. Clear the Password: In the dialog box that appears, simply delete the existing password, which is 123456 . This will remove the password protection.

Delete Password

5. Save Your File: Don't forget to save the changes. Your Excel file is now unprotected.

By following these steps, you can add or remove a password from your Excel file, depending on your security needs.

Part IX - Useful Excel Keyboard Shortcuts

Note 1: Shortcut Keys Explained

When I write a shortcut like "Ctrl + A," it means to press and hold the Ctrl key first, then press the A key. You can release both keys after pressing A.

For example, "Ctrl + Shift + L" for filtering data means:
1. Press and hold the Ctrl key.
2. While holding Ctrl, press and hold the Shift key.
3. While holding Ctrl and Shift, press the L key.
4. You can now release all three keys.

Note 2: Sequential Keys Explained

When I write a shortcut like "Alt, W, F, F," it means to press each key one after the other. You don't need to hold any key in this case.
For example, "Alt, W, F, F" for opening the "Find and Replace" window means:

1. Press the Alt key.
2. Release the Alt key and press the W key.
3. Release the W key and press the F key.
4. Release the F key and press the F key again.

Navigation Shortcuts:
1. Ctrl + Arrow Keys: Move to the edge of data regions. For example, press Ctrl + Down Arrow to jump to the last filled cell in a column.

2. Ctrl + Home: Go to cell A1, the top-left cell in the worksheet.
3. Ctrl + End: Go to the last cell with data in the worksheet.

4. Ctrl + G (or F5): Opens the "Go To" dialog box, allowing you to jump to a specific cell or named range.
Selection Shortcuts:
5. Shift + Arrow Keys: Extend the current selection in the direction of the arrow.

6. Ctrl + Space: Select the entire column of the active cell.
7. Shift + Space: Select the entire row of the active cell.
Editing Shortcuts:
8. F2: Edit the active cell.
9. Ctrl + X: Cut selected cells.
10. Ctrl + C: Copy selected cells.
11. Ctrl + V: Paste cut or copied cells.
12. Ctrl + Z: Undo the last action.
13. Ctrl + Y: Redo the last action.
Formatting Shortcuts:
14. Ctrl + B: Apply or remove bold formatting.
15. Ctrl + I: Apply or remove italic formatting.
16. Ctrl + U: Apply or remove underline formatting.

17. Ctrl + 1: Opens the "Format Cells" dialog box for custom formatting.

Working with Rows and Columns:
18. Ctrl + + (Plus key): Insert new rows or columns.
19. Ctrl + - (Minus key): Delete rows or columns.

20. Alt, E, S, V (Paste Special): Opens the "Paste Special" dialog box for various paste options.

Working with Worksheets:
21. Ctrl + N: Create a new workbook.
22. Ctrl + O: Open an existing workbook.
23. Ctrl + S: Save the active workbook.
24. F12 : Open Save As Option
25. Ctrl + W: Close the active workbook.
26. Ctrl + Page Up / Page Down: Switch between worksheets. Working with Formulas:

26. Alt + = (equal key) : Automatically sum the numbers in the column above the active cell.

27. F4: Repeat the last action (e.g., apply a formula).
28. Ctrl + Shift + Enter: Enter an array formula.
Autofill and Data Entry:
29. Ctrl + D: Fill down (copies the value from the cell above).
30. Ctrl + R: Fill right (copies the value from the cell to the left).

Miscellaneous Shortcuts:
31. Alt + Enter: Start a new line within a cell (useful for cell comments).

32. Ctrl + K: Insert a hyperlink.
33. Ctrl + ; (Semicolon): Insert the current date.
34. Ctrl + Shift + ; (Semicolon): Insert the current time. Working with Charts:
35. F11: Create a new chart sheet.
Pivot Table Shortcuts:
36. Alt + Shift + ← (Left Arrow): Ungroup data in a pivot table.
37. Alt + Shift + → (Right Arrow): Group data in a pivot table. Excel Tables:
38. Ctrl + T: Convert a range into an Excel table.
Cell Comments:
39. Shift + F2: Inset or Edit a cell comment.
Find and Replace:
40. Ctrl + F: Open the "Find" dialog box.
41. Ctrl + H: Open the "Replace" dialog box.
Spell Check:
43. F7: Run the spell checker.
Hyperlinks:
44. Ctrl + Click: Follow a hyperlink.
45. Ctrl + K : Inset Hyperlink

Formula Auditing:
45. Ctrl + [ (Left Square Bracket): Trace precedents.
46. Ctrl + ] (Right Square Bracket): Trace dependents.
47. Ctrl + Shift + } (Right Curly Brace): Select all precedent cells.
48. Ctrl + Shift + { (Left Curly Brace): Select all dependent cells. Workbook Navigation:
49. Ctrl + Tab: Switch between open workbooks.
50. Alt + F4: Close Excel.
Formatting Shortcuts:
51. Ctrl + Shift + L: Apply or remove filter/sort (toggle AutoFilter).
52. Ctrl + 5: Apply or remove strikethrough formatting.
Cell Context Menu:
53. Shift + F10: Show the context menu (right-click menu) for a cell. Workbook Management:
54. Ctrl + F9: Minimize the active workbook.
60. Ctrl + F10: Maximize or restore the active workbook window. Worksheet Management:
61. Ctrl + F6: Switch to the next open workbook window. Cell References:

62. F4: Toggle between relative, absolute, and mixed cell references in a formula.

BONUS TIPS
Bonus Tip#1

Use of Alt + Enter in Excel

In Microsoft Excel, pressing the "Alt + Enter" keyboard shortcut serves a specific purpose:
Alt + Enter: This keyboard shortcut is used to insert a line break or a new line within a cell. It allows you to create multiline text within a single cell.

Here's how to use Alt + Enter :
1. Click on the cell where you want to enter multiline text.
2. Start typing your text.

3. When you want to move to a new line within the same cell, press "Alt + Enter."
4. Continue typing on the new line within the same cell.

Remember that when you use "Alt + Enter" to create multiline text within a cell, the cell may need to be adjusted in height or width to display all the lines correctly. You can do this by manually adjusting the row height and column width or by using the "Wrap Text" feature in Excel to automatically adjust the row height based on the content but column width needs to adjusted to display all lines correctly.

Tips
1. If your text look like this after pressing Alt + Enter or Writing multiline text within a cell :
2.Just Click on Wrap Text to automatically adjust the row height based on the content
Few examples of how to use "Alt + Enter" in Microsoft Excel: 1. Suppose you have a list of addresses in a single cell like this: ➢ A-467, Wazirpur Industrial Area, Delhi - 110052
You can use "Alt + Enter" to format it like this:

This shortcut is handy when you need to enter longer text or notes within a cell or when you want to format text within a cell to make it more readable. It's commonly used for creating labels, adding comments, or providing additional information in Excel cells.

Let your cells do the talking!

Instead of typing your criteria directly into formulas like VLOOKUP or AVERAGEIF, you can put it in a cell and just select that cell in the formula. This is great for two reasons:

• Easy changes: If you want to find results for a different value, just update the cell instead of rewriting the whole formula. Imagine changing "Apple" to "Orange" in one go!

• Formula reuse: Want to apply the same formula to different values? No problem! Just copy the formula and it will automatically adjust based on the selected cell.

For example, if you have "Apple" in cell C2 and you're using VLOOKUP in cell D2, just select C2 instead of typing "Apple" in the formula. Now, changing the value in C2 will instantly update the results in D2!

This tip saves time and makes your formulas more flexible and reusable. So go ahead, let your cells do the talking!

Bonus Tip#3

Print Quality Tip For printing Excel data, a
standard DPI of 300 is generally recommended
for a good balance between print quality and file
size. Adjust based on the content; higher DPI for
detailed graphics, lower for basic text and tables.

Get multiple results with ease!

Want to use the same formula in multiple cells without adjusting it each time? Here's a handy trick:
Step 1: Lock it in!

Have your lookup values in cells like C2, C3, and C4? Place your formula in cell D2 (like VLOOKUP or AVERAGEIF). When you reference your data range in the formula, make it absolute! This means adding dollar signs before the row and column numbers (e.g., $A$1:$B$10). This "locks" the range, so it stays the same when you copy the formula.

Step 2: Copy and paste the magic!

Now, just copy the formula from D2 and paste it in D3, D4, or any other cells you want results for. The absolute reference ensures the formula uses the correct data range, regardless of where you paste it.

Example:
• Lookup values in C2, C3, C4
• Formula in D2: =VLOOKUP(D2,$A$1:$B$10,2,FALSE)
• Copy D2 and paste it in D3, D4, etc.

This way, you only need to write the formula once and get results for all your lookup values! No more tedious rewriting or adjustments. So, remember: lock it in and paste the magic!

Click and go!

Did you know you can apply most commands in Excel just by clicking on them? It's faster and easier than remembering keyboard shortcuts. But remember, before you click your fancy, make sure you've selected the cells or range you want to work with!

Think of it like this: imagine you're ordering food at a restaurant. You wouldn't just yell out your order without pointing to the menu, right? In Excel, it's the same. Select what you want to work on, then click the command you want to use. Simple!

This tip helps you:
• Save time: No more searching for keyboard shortcuts. Click and go!

• Stay organized: Selecting your cells first makes sure the command affects the right area.
• Be confident: Don't worry about memorizing a million shortcuts. Just point and click!
So, remember, click your way to Excel mastery! Choose the cell, then choose your command. It's that easy!